﻿using System;
using System.Collections.Generic;
using KPIS.DBM;
using System.Configuration;
using KPIS.GERP.GFMS.MODEL;
using System.Data;
using KPIS.GERP.GFMS.DAL;
using KPIS.GERP.GFMS.IDAL;
using System.Linq;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_ACM_GENERATE_ACCOUNT_Auto : IDAL_ACM_GENERATE_ACCOUNT_Auto
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();
        D_FIM_MoneyRequests mr = new D_FIM_MoneyRequests();
        D_FIM_Payments pay = new D_FIM_Payments();

        public bool RollbackMoneyReq(int money_req_seq, int emp_seq, out string error)
        {
            var mrModel = mr.GetInfo(money_req_seq);
            string RefNo = mrModel.ref_no;
            string strSQL = "";
            strSQL = @"
select ref_no as code, 'fim_payments' as table_name
from fim_payments
where money_req_seq = @money_req_seq
and record_status not like 'D'
--and (tran_grp_seq is not null or tran_grp_seq <> '')
";

            dbManager.Open();
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@ref_no", RefNo);
            dbManager.AddParameters(1, "@money_req_seq", money_req_seq);
            
            DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            if (dtb.Rows.Count == 0)
            {
                dbManager.BeginTransaction();

                #region Adjust Account

                if (mrModel.tran_grp_seq.HasValue)
                {
                    strSQL = @"
select tran_seq as seq, tran_code as code, 'acm_transection' as table_name
from acm_transection
where ref_no = @ref_no
and record_status not like 'D'
";
                    
                    dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                    if (dtb.Rows.Count > 0)
                    {
                        try
                        {
                            string fiscalPeriod = DateTime.Today.Year.ToString() + "/" + DateTime.Today.Month.ToString();

                            foreach (DataRow dr in dtb.Rows)
                            {
                                int TranSeq = cn.ConvertNullInt(dr["seq"].ToString()) ?? 0;
                                M_ACM_GENERAL_Ledger info = GetHeaderInfo(TranSeq, dbManager);
                                info.fiscal_period = fiscalPeriod;
                                info.tran_type = "A";
                                info.tran_date = DateTime.Today;
                                info.tran_grp_seq = null;
                                info.tran_code = GetNextTranCode(info.bgm_source_seq.ToString(), info.bgm_year.ToString(), "A", dbManager);
                                info.tran_name = "ปรับปรุงบัญชีจากการยกเลิกรายการรอเบิก " + RefNo;
                                info.ref_no = RefNo;
                                info.record_status = "W";
                                info.created_by = emp_seq;
                                info.created_when = DateTime.Now;

                                string NewTranSeq = Insert_Transaction(info, dbManager);

                                IList<M_ACM_GENERAL_Ledger> infoList = GetDtlsFullData(TranSeq, dbManager).OrderBy(o => o.dr_or_cr).ToList();
                                foreach (M_ACM_GENERAL_Ledger infoDtls in infoList)
                                {
                                    infoDtls.tran_seq = cn.ConvertNullInt(NewTranSeq) ?? 0;
                                    infoDtls.dr_or_cr = infoDtls.dr_or_cr == "Dr" ? "Cr" : "Dr";
                                    infoDtls.record_status = "W";
                                    infoDtls.created_by = emp_seq;
                                    infoDtls.created_when = DateTime.Now;
                                    Insert_DB_CR(infoDtls, dbManager);
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            dbManager.RollBackTransaction();
                            error = "[adjust account]" + ex.Message;
                            return false;
                        }
                    }
                }
                #endregion

                #region update fim_money_requests
                try
                {
                    dbManager.CreateParameters(2);
                    dbManager.AddParameters(0, "@money_req_seq", money_req_seq);
                    dbManager.AddParameters(1, "@emp_seq", emp_seq);
                    strSQL = @"
update fim_money_requests
set record_status = 'W'
, tran_grp_seq = null
, updated_by = @emp_seq
, updated_when = getdate()
where money_req_seq = @money_req_seq
";
                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }
                catch (Exception ex)
                {
                    dbManager.RollBackTransaction();
                    error = "[update fim_money_requests]" + ex.Message;
                    return false;
                }
                #endregion

                #region update bgm_project_operation_report
                try
                {
                    strSQL = @"
update bgm_project_operation_report
set record_status = 'D'
, updated_by = 1
, updated_when = getdate()
where money_req_dtls_seq in (select money_req_dtls_seq
	from fim_money_request_dtls
	where money_req_seq = @money_req_seq
    and record_status not like 'D'
	)
";
                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }
                catch (Exception ex)
                {
                    dbManager.RollBackTransaction();
                    error = "[update bgm_project_operation_report]" + ex.Message;
                    return false;
                }
                #endregion

                dbManager.CommitTransaction();
                dbManager.Dispose();
                error = "ย้อนสถานะเรียบร้อย";
                return true;
            }
            else
            {
                string strErrorId = "";
                foreach (DataRow dr in dtb.Rows)
                {
                    strErrorId += "[Table = " + dr[1].ToString() + ", Code = " + dr[0].ToString() + "] ";
                }

                error = "รายการนี้ถูกนำไปดำเนินการต่อแล้ว โดย " + strErrorId;
                return false;
            }
        }

        public bool RollbackPayment(int pay_seq, int emp_seq, out string error)
        {
            var payModel = pay.GetInfo(pay_seq);
            string RefNo = payModel.ref_no;
            string strSQL = "";
            strSQL = @"
select ref_no as code, 'fim_payments' as table_name
from fim_payments
where (post_pay_seq = @pay_seq or tax_pay_seq = @pay_seq)
and record_status not like 'D'
";

            dbManager.Open();
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@pay_seq", pay_seq);
            DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            if (dtb.Rows.Count == 0)
            {
                dbManager.BeginTransaction();

                #region Adjust Account

                if (payModel.tran_grp_seq.HasValue)
                {
                    strSQL = @"
select tran_code as code, 'acm_transection' as table_name
from acm_transection
where tran_grp_seq = (select tran_grp_seq from fim_payments where pay_seq = @pay_seq)
and record_status not like 'D'
";
                    dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                    if (dtb.Rows.Count > 0)
                    {
                        try
                        {
                            string fiscalPeriod = DateTime.Today.Year.ToString() + "/" + DateTime.Today.Month.ToString();

                            foreach (DataRow dr in dtb.Rows)
                            {
                                int TranSeq = cn.ConvertNullInt(dr["seq"].ToString()) ?? 0;
                                M_ACM_GENERAL_Ledger info = GetHeaderInfo(TranSeq, dbManager);
                                info.fiscal_period = fiscalPeriod;
                                info.tran_type = "A";
                                info.tran_date = DateTime.Today;
                                info.tran_grp_seq = null;
                                info.tran_code = GetNextTranCode(info.bgm_source_seq.ToString(), info.bgm_year.ToString(), "A", dbManager);
                                info.tran_name = "ปรับปรุงบัญชีจากการยกเลิกรายการจ่ายเงิน " + RefNo;
                                info.ref_no = RefNo;
                                info.record_status = "W";
                                info.created_by = emp_seq;
                                info.created_when = DateTime.Now;

                                string NewTranSeq = Insert_Transaction(info, dbManager);

                                IList<M_ACM_GENERAL_Ledger> infoList = GetDtlsFullData(TranSeq, dbManager).OrderBy(o => o.dr_or_cr).ToList();
                                foreach (M_ACM_GENERAL_Ledger infoDtls in infoList)
                                {
                                    infoDtls.tran_seq = cn.ConvertNullInt(NewTranSeq) ?? 0;
                                    infoDtls.dr_or_cr = infoDtls.dr_or_cr == "Dr" ? "Cr" : "Dr";
                                    infoDtls.record_status = "W";
                                    infoDtls.created_by = emp_seq;
                                    infoDtls.created_when = DateTime.Now;
                                    Insert_DB_CR(infoDtls, dbManager);
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            dbManager.RollBackTransaction();
                            error = "[adjust account]" + ex.Message;
                            return false;
                        }
                    }
                }
                #endregion

                #region update fim_payments
                try
                {
                    dbManager.CreateParameters(1);
                    dbManager.AddParameters(0, "@pay_seq", pay_seq);
                    strSQL = @"
update fim_payments
set record_status = 'D'
, updated_by = 1
, updated_when = getdate()
where pay_seq = @pay_seq
";
                    dbManager.BeginTransaction();
                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }
                catch (Exception ex)
                {
                    dbManager.RollBackTransaction();
                    error = "[update fim_payments]" + ex.Message;
                    return false;
                }
                #endregion

                #region update bgm_project_operation_report
                try
                {
                    strSQL = @"
update bgm_project_operation_report
set record_status = 'D'
, updated_by = 1
, updated_when = getdate()
where pay_seq = @pay_seq
";
                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }
                catch (Exception ex)
                {
                    dbManager.RollBackTransaction();
                    error = "[update bgm_project_operation_report]" + ex.Message;
                    return false;
                }
                #endregion

                #region update fim_payment_cheques
                try
                {
                    strSQL = @"
update fim_payment_cheques
set record_status = 'D'
, updated_by = 1
, updated_when = getdate()
where pay_seq = @pay_seq
";
                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }
                catch (Exception ex)
                {
                    dbManager.RollBackTransaction();
                    error = "[update fim_payment_cheques]" + ex.Message;
                    return false;
                }
                #endregion

                #region update fim_payments_payinpack
                try
                {
                    strSQL = @"
update fim_payments_payinpack
set record_status = 'D'
, updated_by = 1
, updated_when = getdate()
where pay_seq = @pay_seq
";
                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }
                catch (Exception ex)
                {
                    dbManager.RollBackTransaction();
                    error = "[update fim_payments_payinpack]" + ex.Message;
                    return false;
                }
                #endregion

                dbManager.CommitTransaction();
                error = "ย้อนสถานะเรียบร้อย";
                return true;
            }
            else
            {
                string strErrorId = "";
                foreach (DataRow dr in dtb.Rows)
                {
                    strErrorId += "[Table = " + dr[1].ToString() + ", Code = " + dr[0].ToString() + "] ";
                }

                error = "รายการนี้ถูกนำไปดำเนินการต่อแล้ว โดย " + strErrorId;
                return false;
            }
        }

        public void Insert_DB_CR(M_ACM_GENERAL_Ledger info, DBManager dbManager) //acm_transection_dtls
        {
            string strSQL;
            try
            {
                //dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@tran_seq", info.tran_seq);
                dbManager.AddParameters(1, "@account_seq", info.account_seq);
                dbManager.AddParameters(2, "@dr_or_cr", info.dr_or_cr);
                dbManager.AddParameters(3, "@amount", info.amount);
                dbManager.AddParameters(4, "@record_status", info.record_status);
                dbManager.AddParameters(5, "@created_by", info.created_by);
                dbManager.AddParameters(6, "@created_when", info.created_when);

                strSQL = " INSERT INTO acm_transection_dtls "
                        + "(tran_seq,account_seq,dr_or_cr,amount,record_status,created_by,created_when)"
                        + " VALUES (@tran_seq,@account_seq,@dr_or_cr,@amount,@record_status,@created_by,@created_when) ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                //dbManager.Dispose();
            }
        }

        public string Insert_Transaction(M_ACM_GENERAL_Ledger info, DBManager dbManager)
        {
            string strSQL;
            string strSeq;
            try
            {
                //dbManager.Open();
                dbManager.CreateParameters(14);
                //dbManager.BeginTransaction();
                dbManager.AddParameters(0, "@fiscal_period", info.fiscal_period);
                dbManager.AddParameters(1, "@tran_grp_seq", info.tran_grp_seq);
                dbManager.AddParameters(2, "@tran_code", info.tran_code);
                dbManager.AddParameters(3, "@tran_date", info.tran_date);
                dbManager.AddParameters(4, "@work_area_seq", info.work_area_seq);
                dbManager.AddParameters(5, "@bgm_source_seq", info.bgm_source_seq);
                dbManager.AddParameters(6, "@tran_name", info.tran_name);
                dbManager.AddParameters(7, "@remarks", info.remark);
                dbManager.AddParameters(8, "@record_status", info.record_status);
                dbManager.AddParameters(9, "@created_by", info.created_by);
                dbManager.AddParameters(10, "@created_when", info.created_when);
                dbManager.AddParameters(11, "@tran_type", info.tran_type);
                dbManager.AddParameters(12, "@ref_no", info.ref_no);
                dbManager.AddParameters(13, "@bgm_year", info.bgm_year);

                strSQL = " INSERT INTO acm_transection "
                        + " (fiscal_period,tran_grp_seq,tran_code,tran_date,work_area_seq,bgm_source_seq "
                        + " ,tran_name,remarks,record_status,created_by,created_when,tran_type,ref_no, bgm_year)"
                        + "  VALUES "
                        + " (@fiscal_period,@tran_grp_seq,@tran_code,@tran_date,@work_area_seq,@bgm_source_seq "
                        + " ,@tran_name,@remarks,@record_status,@created_by,@created_when,@tran_type,@ref_no, @bgm_year)"
                        + " select scope_identity();";
                strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                //dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                //strSQL = "select last_insert_id() as last_seq from acm_transection";
                //strSQL = "INSERT INTO icm_bid_offer_collateral"
                //    + " (icm_col_type_seq, icm_bid_off_col_ref_no, icm_bid_off_col_amount"
                //    + ", icm_bid_off_col_descr, icm_bid_off_seq, receipt_no"
                //    + ", created_by, created_when)"
                //    + " VALUES(@icm_col_type_seq"
                //    + ", @icm_bid_off_col_ref_no"
                //    + ", @icm_bid_off_col_amount"
                //    + ", @icm_bid_off_col_descr"
                //    + ", @icm_bid_off_seq"
                //    + ", @receipt_no"

                //    + ", @created_by"
                //    + ", @created_when"
                //    + " );"
                //    + " select scope_identity();";
                //var strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                //return strSeq;

                //strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
                //dbManager.CommitTransaction();
            }
            catch (Exception ex)
            {
                //dbManager.RollBackTransaction();
                throw (ex);
            }
            finally
            {
                //dbManager.Dispose();
            }

            return strSeq;
        }

        public M_ACM_GENERAL_Ledger GetHeaderInfo(int tran_seq, DBManager dbManager)
        {
            DataRowCollection drc = null;
            M_ACM_GENERAL_Ledger info = new M_ACM_GENERAL_Ledger();
            try
            {
                //dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@tran_seq", tran_seq);

                string strSQL = "SELECT bgm_year, tran_code, tran_seq, tran_type, work_area_seq"
                    + ", bgm_source_seq, tran_date, tran_name, remarks, record_status"
                    + ", case record_status"
                        + " when 'A' then 'Posted'"
                        + " else 'รอ Post'"
                        + " end as record_status_name"
                    + " FROM acm_transection"
                    + " WHERE tran_seq = @tran_seq";


                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.tran_seq = Convert.ToInt32(drc[0]["tran_seq"]);
                    info.bgm_year = cn.ConvertNullInt(drc[0]["bgm_year"]);
                    info.tran_code = drc[0]["tran_code"].ToString();
                    info.tran_type = drc[0]["tran_type"].ToString();
                    info.work_area_seq = cn.ConvertNullInt(drc[0]["work_area_seq"]);
                    info.bgm_source_seq = cn.ConvertNullInt(drc[0]["bgm_source_seq"]);
                    info.tran_date = cn.ConvertNullDateTime(drc[0]["tran_date"]);
                    info.tran_name = drc[0]["tran_name"].ToString();
                    info.remark = drc[0]["remarks"].ToString();
                    info.record_status = drc[0]["record_status"].ToString();
                    info.record_status_name = drc[0]["record_status_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                //dbManager.Dispose();
            }
            return info;
        }

        public IList<M_ACM_GENERAL_Ledger> GetDtlsFullData(Nullable<int> tran_seq, DBManager dbManager)
        {
            DataRowCollection drc = null;
            IList<M_ACM_GENERAL_Ledger> infoList = new List<M_ACM_GENERAL_Ledger>();
            try
            {
                //dbManager.Open();
                string strSQL = "";
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(1, "@bgm_year", bgm_year);
                //dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                //dbManager.AddParameters(3, "@work_area_seq", work_area_seq);
                //dbManager.AddParameters(4, "@tran_type", tran_type);
                //dbManager.AddParameters(5, "@tran_code", tran_code);
                //dbManager.AddParameters(6, "@dtStart", dtStart);
                //dbManager.AddParameters(7, "@dtEnd", dtEnd);
                dbManager.AddParameters(1, "@tran_seq", tran_seq);

                strSQL = "SELECT td.tran_dtls_seq, td.tran_seq" //, t.fiscal_period, t.tran_grp_seq"
                    //+ ", t.tran_code, t.tran_type, t.tran_date, t.tran_name, t.bgm_year, t.work_area_seq, t.bgm_source_seq"
                    + ", td.account_seq, td.dr_or_cr, acc.account_code, acc.account_name, td.amount"
                    + ", case td.dr_or_cr"
                        + " when 'Dr' then td.amount"
                        + " when 'Cr' then null"
                        + " end as debit_amount"
                    + ", case td.dr_or_cr"
                        + " when 'Dr' then null"
                        + " when 'Cr' then td.amount"
                        + " end as credit_amount"
                    + " FROM acm_transection_dtls td"
                    //+ " Left Join acm_transection_dtls td ON t.tran_seq = td.tran_seq and td.record_status not like @delete_status"
                    + " Left Join acm_mas_account acc ON td.account_seq = acc.account_seq"
                    + " WHERE td.record_status not like @delete_status"
                    //+ (bgm_year != null ? " and t.bgm_year = @bgm_year" : "")
                    //+ (bgm_source_seq != null ? " and t.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (work_area_seq != null ? " and t.work_area_seq = @work_area_seq" : "")
                    //+ (!string.IsNullOrEmpty(tran_type) ? " and t.tran_type = @tran_type" : "")
                    //+ (!string.IsNullOrEmpty(tran_code) ? " and t.tran_code like '%" + tran_code + "%'" : "")
                    //+ (dtStart != null ? " and t.tran_date >= @dtStart" : "")
                    //+ (dtEnd != null ? " and t.tran_date <= @dtEnd" : "")
                    + (tran_seq != null ? " and td.tran_seq = @tran_seq" : "")

                    + " ORDER BY td.tran_dtls_seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_GENERAL_Ledger info = new M_ACM_GENERAL_Ledger();
                        info.tran_dtls_seq = cn.ConvertNullInt(drc[i]["tran_dtls_seq"]);
                        info.tran_seq = Convert.ToInt32(drc[i]["tran_seq"]);
                        //info.tran_type = drc[i]["tran_type"].ToString();
                        info.account_seq = cn.ConvertNullInt(drc[i]["account_seq"]);
                        info.account_code = drc[i]["account_code"].ToString();
                        info.account_name = drc[i]["account_name"].ToString();
                        //info.tran_code = drc[i]["tran_code"].ToString();
                        //info.tran_date = cn.ConvertNullDateTime(drc[i]["tran_date"]);
                        //info.tran_name = drc[i]["tran_name"].ToString();
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.dr_or_cr = drc[i]["dr_or_cr"].ToString();
                        info.debit_amount = cn.ConvertNullDouble(drc[i]["debit_amount"]);
                        info.credit_amount = cn.ConvertNullDouble(drc[i]["credit_amount"]);
                        //info.bgm_source_seq = cn.ConvertNullInt(drc[i]["account_seq"]);
                        //info.bgm_year = cn.ConvertNullInt(drc[i]["bgm_year"]);
                        //info.work_area_seq = cn.ConvertNullInt(drc[i]["work_area_seq"]);
                        //info.fiscal_period = drc[i]["fiscal_period"].ToString();
                        //info.tran_grp_seq = cn.ConvertNullInt(drc[i]["tran_grp_seq"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                //dbManager.Dispose();
            }
            return infoList;
        }

        public IList<M_ACM_GENERATE_ACCOUNT_Auto> GetListMaster(string seq,string reftype)
        {
            //Nullable<int> nbgm_source_seq = cn.ConvertNullInt(bgm_source_seq);
            //Nullable<int> nwork_area_seq = cn.ConvertNullInt(work_area_seq);
            //Nullable<int> nwork_area_seq = 1;
            DataRowCollection drc = null;
            IList<M_ACM_GENERATE_ACCOUNT_Auto> infoList = new List<M_ACM_GENERATE_ACCOUNT_Auto>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                //dbManager.AddParameters(0, "@ref_type", info.ref_type);
                //dbManager.AddParameters(0, "@bgm_source_seq", nbgm_source_seq);
                //dbManager.AddParameters(1, "@work_area_seq", 1);
                dbManager.AddParameters(0, "@DeleteStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@ApproveStatus", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@reftype", reftype);
                dbManager.AddParameters(3, "@seq", cn.ConvertNullInt(seq));

                ///Code จาก Module GAMS ไม่เอา bgm_soure_seq , work_area_seq, event_seq  เพิ่ม where ref_type and ref_seq
                string strSQL =
                    // petition จ่ายผ่านส่วนราชการ =========================================================================
                    "SELECT * FROM"
                    + " (SELECT 'fim_petitions' as ref_type"
                    + ", 'G' as tran_type"
                    + ", pt.ref_no"
                    + ", pt.petition_date as tran_date"
                    + ", concat('รายการขอเบิกเลขที่ ', coalesce(pt.ref_no, '')) as tran_name"
                    //+ ", 1 as work_area_seq"
                    //+ ", pt.bgm_source_seq"
                    + ", pt.petition_seq as ref_seq"
                    + ", pt.bgm_year"

                    + " FROM fim_petitions pt"
                    + " WHERE pt.record_status = @ApproveStatus"
                    + " AND pt.tran_grp_seq is null"
                    + " AND pt.direct_pay = '0'"

                    // petition จ่ายตรง =========================================================================
                    + " union all "

                    + " SELECT 'fim_petitions' as ref_type"
                    + ", 'G' as tran_type"
                    + ", pt.ref_no"
                    + ", pt.petition_date as tran_date"
                    + ", concat('รายการขอเบิกเลขที่ ', coalesce(pt.ref_no, '')) as tran_name"
                    //+ ", 1 as work_area_seq"
                    //+ ", pt.bgm_source_seq"
                    + ", pt.petition_seq as ref_seq"
                    + ", pt.bgm_year"
                    //+ " , concat(coalesce(bgm_source_code, ''), ' : ', coalesce(bgm_source_name, ''))  as source_name "
                    //+ " , concat(coalesce(work_area_id, ''), ' : ', coalesce(work_area_name, ''))  as area_name "
                    //+ " , '2' as dtls_type"
                    + " FROM fim_petitions pt "
                    //+ " LEFT JOIN bgm_mas_source bgm_source ON "
                    //    + " pt.bgm_source_seq = bgm_source.bgm_source_seq "
                    //+ " LEFT JOIN hrm_mp_work_areas work_areas ON "
                    //    + " 1 = work_areas.work_area_seq "
                    + " WHERE pt.record_status = 'A' "
                    + " AND pt.tran_grp_seq is null  AND pt.direct_pay = '1' "

                    // money_request =========================================================================
                    + " union all"

                    + " SELECT 'fim_money_requests' as ref_type"
                    + ", 'G' as tran_type"
                    + ", mr.ref_no"
                    + ", mr.money_req_date as tran_date"
                    + ", mr.money_req_name as tran_name"
                    //+ ", mr.work_area_seq"
                    //+ ", mr.bgm_source_seq"
                    + ", mr.money_req_seq as ref_seq"
                    + ", mr.bgm_year"

                    + " FROM fim_money_requests mr"

                    + " WHERE mr.record_status = 'A'"
                    + " AND mr.tran_grp_seq is null"
                    //+ " AND mr.event_seq not in (16, 18)"
                    //+ (!string.IsNullOrEmpty(bgm_source_seq) ? " AND mr.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (!string.IsNullOrEmpty(work_area_seq) ? " AND mr.work_area_seq = @work_area_seq" : "")

                    // payment =========================================================================
                    + " union all "// จ่ายเงินสด และโอน

                    + " SELECT 'fim_payments' as ref_type"
                    + ", 'P' as tran_type"
                    + ", pay.ref_no"
                    + ", pay.real_pay_date as tran_date"
                    + ", pay.pay_name as tran_name"
                    + ", pay.pay_seq as ref_seq"
                    + ", pay.bgm_year"

                    + " FROM fim_payments pay"
                    + " WHERE pay.record_status = 'A'"
                    + " AND pay.tran_grp_seq is null"
                    //+ " AND (pet.direct_pay is null OR pet.direct_pay = '0')"
                    + " AND pay.method_seq in (1, 3)"
                    + " AND pay.real_pay_date is not null"
                    + " AND pay.event_seq <> 19"
                    //+ (!string.IsNullOrEmpty(work_area_seq) ? " AND pay.work_area_seq = @work_area_seq" : "")

                    + " union all "// จ่ายเช็ค

                    + @"
SELECT 'fim_payments' as ref_type
, 'P' as tran_type
, pay.ref_no
, cheq.pay_date as tran_date
, pay.pay_name + '[ เช็คเลขที่ '+ c.cheque_no + ' ]' as tran_name
, pay.pay_seq as ref_seq
, pay.bgm_year
FROM fim_payments pay
left join (
	select pc.pay_seq, STUFF((
		SELECT N', ' + CAST(c.cheque_no AS VARCHAR(255))
		from fim_payment_cheques f1
		left join fim_cheques c on f1.cheque_seq = c.cheque_seq
		WHERE pc.pay_seq = f1.pay_seq
			and pc.record_status <> 'D'
			and c.record_status <> 'D'
			and c.cheque_status <> '0'
		FOR XML PATH ('')), 1, 2, '') AS cheque_no
	from fim_payment_cheques pc
	where pc.record_status <> 'D'

	GROUP BY pc.pay_seq, pc.record_status
) c on c.pay_seq = pay.pay_seq
left join fim_payment_cheques pc on pay.pay_seq = pc.pay_seq
left join fim_cheques cheq on pc.cheque_seq = cheq.cheque_seq

WHERE pay.record_status = 'A'
AND pay.tran_grp_seq is null
AND pay.method_seq in (2)
AND cheq.pay_date is not null
AND pay.event_seq <> 19
"
                    + " union all "// จ่าย pay in pack

                    + @"
SELECT 'fim_payments' as ref_type
, 'P' as tran_type
, pay.ref_no
, pip.pip_date as tran_date
, pay.pay_name + ' [pip : ' + pip.pip_no + ']' as tran_name
, pay.pay_seq as ref_seq
, pay.bgm_year
FROM fim_payments pay
left join fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq

WHERE pay.record_status = 'A'
AND pay.tran_grp_seq is null
AND pay.method_seq in (4)
AND pip.pip_date is not null
AND pay.event_seq <> 19
"

                    + " union all "// โอนด้วยเช็ค

                    + @"
SELECT 'fim_payments' as ref_type
, 'P' as tran_type
, tf.ref_no
, cheq.pay_date as tran_date
, tf.transfer_name + '[ เช็คเลขที่ '+ CAST(cheq.cheque_no AS VARCHAR(255)) + ' ]' as tran_name
, tf.pay_seq as ref_seq
, tf.bgm_year
FROM fim_transfers tf
left join fim_cheques cheq on tf.cheque_seq = cheq.cheque_seq

WHERE tf.record_status <> 'D'
AND tf.tran_grp_seq is null
-- AND cheq.pay_date is not null
"
                    //+ " AND pay.method_seq = 2"
                    //+ " AND c.pay_date is not null"
                    //+ " AND c.cheque_status = 2"
                    //+ (!string.IsNullOrEmpty(work_area_seq) ? " AND pay.work_area_seq = @work_area_seq" : "")

                    // receive =========================================================================
                    + " union all "

                    + " SELECT 'fim_receives' as ref_type"
                    + ", 'R' as tran_type"
                    + ", r.ref_no"
                    + ", r.receive_date as tran_date"
                    + ", case receive_mode when 1 then cast(concat('รับเงินเบิกเกินส่งคืน ใบเสร็จเลขที่ ', coalesce(r.receipt_no, '')) as varchar)"
                        + " when 2 then cast(concat('รายการรับเงิน ใบเสร็จเลขที่ ', coalesce(r.receipt_no, '')) as varchar)"
                        + " when 3 then cast(concat('รับเงินจากรายการขอเบิก เลขที่ ', coalesce(pet.petition_no, '')) as varchar)"
                        + " end as tran_name"
                    //+ ", r.work_area_seq"
                    //+ ", r.bgm_source_seq"
                    + ", r.receive_seq as ref_seq"
                    + ", r.bgm_year"

                    + " FROM fim_receives r"
                    + " LEFT JOIN fim_petitions pet on r.petition_seq = pet.petition_seq"
                    + " WHERE r.record_status not like 'D'"
                    + " and (COALESCE(r.receipt_no, '') not like '' or receive_mode = '3')"
                    + " AND r.tran_grp_seq is null"
                    //+ (!string.IsNullOrEmpty(bgm_source_seq) ? " AND r.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (!string.IsNullOrEmpty(work_area_seq) ? " AND r.work_area_seq = @work_area_seq" : "")

                    // return =========================================================================
                    + " union all "

                    + " SELECT 'fim_money_returns' as ref_type"
                    + ", 'G' as tran_type"
                    + ", ret.ref_no"
                    + ", ret.return_date as tran_date"
                    + ", concat('รายการหักล้าง ', coalesce(mr.money_req_name, '')) as tran_name"
                    //+ ", mr.work_area_seq"
                    //+ ", mr.bgm_source_seq"
                    + ", ret.return_seq as ref_seq"
                    + ", mr.bgm_year"

                    + " FROM fim_money_returns ret"
                    + " Left Join fim_money_requests AS mr ON ret.money_req_seq = mr.money_req_seq"
                    + " WHERE ret.record_status = 'A'"
                    + " AND ret.tran_grp_seq is null"
                    //+ (!string.IsNullOrEmpty(bgm_source_seq) ? " AND mr.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (!string.IsNullOrEmpty(work_area_seq) ? " AND mr.work_area_seq = @work_area_seq" : "")

                    //// delivery_note =========================================================================
                    + " union all "

                    + " SELECT 'icm_delivery_note' as ref_type"
                    + ", 'G' as tran_type"
                    + ", dn.Code as ref_no"
                    + ", dn.ReceiveDate as tran_date"
                    + ", dn.Name as tran_name"
                    //+ ", null as work_area_seq"
                    //+ ", pe.bgm_source_seq"
                    + ", dn.DnId as ref_seq"
                    + ", dn.BgYear as bgm_year"
                    + " FROM IcmDeliveryNote dn"
                    + " LEFT JOIN bgm_project_operation_expense ope on dn.ProjOperExpId = ope.bgm_proj_oper_exp_seq"
                    + " LEFT JOIN bgm_project_expense pe on ope.bgm_proj_exp_seq = pe.bgm_proj_exp_seq"
                    //+ " LEFT JOIN bgm_mas_source bgm_source ON 1 = bgm_source.bgm_source_seq "
                    //+ " LEFT JOIN hrm_mp_work_areas work_areas ON 1 = work_areas.work_area_seq "
                    + " WHERE dn.RecordStatus = 'A'"
                    + " AND dn.TranGrpId is null"
                    //+ (!string.IsNullOrEmpty(bgm_source_seq) ? " AND pe.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (!string.IsNullOrEmpty(work_area_seq) ? " AND mr.work_area_seq = @work_area_seq" : "")

                    // transfer =========================================================================
                    + " union all "

                    + " SELECT 'fim_transfers' as ref_type"
                    + ", 'G' as tran_type"
                    + ", t.ref_no"
                    + ", t.transfer_date as tran_date"
                    + ", concat(' ', coalesce(t.transfer_name, '')) as tran_name"
                    //+ ", 1 as work_area_seq"
                    //+ ", 1 as bgm_source_seq"
                    + ", t.transfer_seq as ref_seq"
                    + ", t.bgm_year"

                    + " FROM fim_transfers t"
                    //+ " Left Join fim_money_requests AS mr ON ret.money_req_seq = mr.money_req_seq"
                    + " WHERE t.record_status not like 'D'"
                    + " AND t.tran_grp_seq is null"
                    //+ (!string.IsNullOrEmpty(bgm_source_seq) ? " AND t.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (!string.IsNullOrEmpty(work_area_seq) ? " AND mr.work_area_seq = @work_area_seq" : "");
                    + ") t"
                    + " WHERE ref_type=@reftype"
                    + " AND ref_seq =@seq"
                    + " order by tran_date";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_GENERATE_ACCOUNT_Auto info = new M_ACM_GENERATE_ACCOUNT_Auto();
                        info.ref_type = drc[i]["ref_type"].ToString();
                        info.tran_type = drc[i]["tran_type"].ToString();
                        info.ref_no = drc[i]["ref_no"].ToString();
                        info.tran_date = cn.ConvertNullDateTime(drc[i]["tran_date"]);
                        info.tran_name = drc[i]["tran_name"].ToString();
                        //info.work_area_seq = cn.ConvertNullInt(drc[i]["work_area_seq"]);
                        //info.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        info.ref_seq = cn.ConvertNullInt(drc[i]["ref_seq"]);
                        info.bgm_year = cn.ConvertNullInt(drc[i]["bgm_year"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public IList<M_ACM_GENERATE_ACCOUNT_Auto> GetListDetail(Nullable<Int32> ref_seq, string ref_type)
        {
            DataTable dtb = null;
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@ref_seq", ref_seq);
            IList<M_ACM_GENERATE_ACCOUNT_Auto> infoList = new List<M_ACM_GENERATE_ACCOUNT_Auto>();
            try
            {
                dbManager.Open();
                string strSQL = "";
                string strEventSeq = "";

                switch (ref_type)
                {
                    #region fim_petitions
                    case "fim_petitions":
                        strSQL = "SELECT ba.wait_income_bb_account_seq as account_seq"
                            + ", acc.account_code"
                            + ", acc.account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", ptd.amount as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_petitions pt"
                            + " LEFT JOIN (select petition_seq"
                                + " , sum(coalesce(amount_real, 0)) as amount"
                                + " from fim_petition_dtls"
                                + " where record_status not like 'D'"
                                + " group by petition_seq"
                                + " ) ptd ON pt.petition_seq = ptd.petition_seq"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc ON ba.wait_income_bb_account_seq = acc.account_seq"
                            + " WHERE pt.petition_seq = @ref_seq"

                            + " union all"

                            + " SELECT ba.income_bb_account_seq as account_seq"
                            + ", acc.account_code"
                            + ", acc.account_name"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", ptd.amount as amount_cr"
                            + " FROM fim_petitions pt"
                            + " LEFT JOIN (select petition_seq"
                                + " , sum(coalesce(amount_real, 0)) as amount"
                                + " from fim_petition_dtls"
                                + " where record_status not like 'D'"
                                + " group by petition_seq"
                                + " ) ptd ON pt.petition_seq = ptd.petition_seq"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc ON ba.income_bb_account_seq = acc.account_seq"
                            + " WHERE pt.petition_seq = @ref_seq";
                        break;
                    #endregion
                    #region fim_money_requests
                    case "fim_money_requests":
                        strSQL = "SELECT event_seq"
                            + " FROM fim_money_requests"
                            + " WHERE money_req_seq = @ref_seq";
                        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                        if (dtb.Rows.Count > 0)
                        {
                            strEventSeq = dtb.Rows[0]["event_seq"].ToString();
                        }

                        switch (strEventSeq)
                        {
                            case "11":
                                strSQL = "SELECT ba.direct_pay_account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(mrd.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + ", sum(coalesce(mrd.amount_vat, 0)) as amount_vat"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.direct_pay_account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq"

                                    + " UNION ALL"

                                    + " SELECT ba.dn_cr_account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", mrd.amount as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.dn_cr_account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq";
                                break;
                            case "28":
                                strSQL = "SELECT exp.account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(mrd.amount, 0) - coalesce(mrd.amount_vat, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq, mrd.bgm_proj_oper_exp_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + ", sum(coalesce(mrd.amount_vat, 0)) as amount_vat"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq, mrd.bgm_proj_oper_exp_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
                                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                                    + " LEFT JOIN acm_mas_account acc ON exp.account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq"

                                    + " UNION ALL"

                                    + " SELECT ba.payroll_tax_account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", mrd.amount as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.payroll_tax_account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq";
                                break;
                            case "12":
                            case "14":
                            case "15":
                            case "17":
                            case "18":
                            default:
                                strSQL = "SELECT exp.account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(mrd.amount, 0) - coalesce(mrd.amount_vat, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq, mrd.bgm_proj_oper_exp_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + ", sum(coalesce(mrd.amount_vat, 0)) as amount_vat"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq, mrd.bgm_proj_oper_exp_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
                                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                                    + " LEFT JOIN acm_mas_account acc ON exp.account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq"

                                    + " UNION ALL"

                                    + " SELECT ba.suspense_input_tax_account_seq as account_seq"
                                    + ", acc2.account_code as account_code"
                                    + ", acc2.account_name as account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(mrd.amount_vat, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq"
                                        + ", sum(coalesce(mrd.amount_vat, 0)) as amount_vat"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.suspense_input_tax_account_seq = acc2.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq"

                                    + " UNION ALL"

                                    + " SELECT ba.remain_account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", mrd.amount as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.remain_account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq";
                                break;
                        }
                        break;
                    #endregion
                    #region fim_payments
                    case "fim_payments":
                        bool chkTaxInvoice = true;
                        strSQL = "SELECT p.event_seq, mr.chk_tax_invoice, dn.ChkTaxInvoice"
                            + " from fim_payments p"
                            + " left join fim_money_requests mr on p.money_req_seq = mr.money_req_seq"
                            + " left join IcmDeliveryNote dn on mr.icm_dn_seq = dn.DnId"
                            + " WHERE pay_seq = @ref_seq";
                        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                        if (dtb.Rows.Count > 0)
                        {
                            strEventSeq = dtb.Rows[0]["event_seq"].ToString();
                            if (strEventSeq == "11")
                            {
                                chkTaxInvoice = Convert.ToBoolean(cn.ConvertNullInt(dtb.Rows[0]["ChkTaxInvoice"]) ?? 1);
                            }
                            else
                            {
                                chkTaxInvoice = Convert.ToBoolean(cn.ConvertNullInt(dtb.Rows[0]["chk_tax_invoice"]) ?? 1);
                            }
                        }

                        strSQL = "";

                        switch (strEventSeq)
                        {
                            case "11":
                                // ค่าใช้จ่าย
                                strSQL = "SELECT ba.dn_cr_account_seq as account_seq"
                                    + ", acc1.account_code as account_code"
                                    + ", acc1.account_name account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(pay.amount, 0) + coalesce(pay.tax, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON ba.dn_cr_account_seq = acc1.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"
                                    // เครดิต เงินสด/เงินฝาก
                                    + " SELECT case pay.method_seq"
                                        + " when 1 then ba.cash_account_seq"
                                        + " when 2 then bacc.account_seq"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then bacc3.account_seq"
                                                        + " when '1' then ba.income_bb_account_seq"
                                                        + " end"
                                        + " when 4 then bacc2.account_seq"
                                        + " end as account_seq"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_code"
                                        + " when 2 then acc1.account_code"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_code"
                                                        + " when '1' then acc3.account_code"
                                                        + " end"
                                        + " when 4 then acc4.account_code"
                                        + " end as account_code"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_name"
                                        + " when 2 then acc1.account_name"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_name"
                                                        + " when '1' then acc3.account_name"
                                                        + " end"
                                        + " when 4 then acc4.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.amount as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                        + " and pc.record_status not like 'D'"
                                    + " LEFT JOIN fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq"
                                        + " and pip.record_status not like 'D'"
                                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on pip.bank_acc_seq = bacc2.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc3 on pay.from_bank_acc_id = bacc3.bank_acc_seq"
                                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                                    + " LEFT JOIN acm_mas_account acc4 ON bacc2.account_seq = acc4.account_seq"
                                    + " LEFT JOIN acm_mas_account acc5 ON bacc3.account_seq = acc5.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"

                                    + " SELECT case when c.ConGrpId is null or c.ConGrpId = 47 then ba.personal_tax_account_seq"
                                        + " else ba.company_tax_account_seq"
                                        + " end as account_seq"
                                    + ", case when c.ConGrpId is null or c.ConGrpId = 47 then acc1.account_code"
                                        + " else acc2.account_code"
                                        + " end as account_code"
                                    + ", case when c.ConGrpId is null or c.ConGrpId = 47 then acc1.account_name"
                                        + " else acc2.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.tax as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN IcmMasContact c ON pay.contact_seq = c.ConId"
                                    + " LEFT JOIN acm_mas_account acc1 ON ba.personal_tax_account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.company_tax_account_seq = acc2.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    ;
                                break;
                            case "21":
                                strSQL = "SELECT acc.account_seq"
                                    + ", acc.account_code"
                                    + ", acc.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(tax.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " left join (select pay.tax_pay_seq"
                                        + ", sum(pay.tax) as amount"
                                        + " from fim_payments pay"
                                        + " left join IcmMasContact c on pay.contact_seq = c.ConId"
                                        + " where c.ConGrpId = 47"
                                        + " group by pay.tax_pay_seq"
                                        + ") tax on pay.pay_seq = tax.tax_pay_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.personal_tax_account_seq = acc.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"

                                    + " SELECT acc.account_seq"
                                    + ", acc.account_code"
                                    + ", acc.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(tax.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " left join (select pay.tax_pay_seq"
                                        + ", sum(pay.tax) as amount"
                                        + " from fim_payments pay"
                                        + " left join IcmMasContact c on pay.contact_seq = c.ConId"
                                        + " where c.ConGrpId <> 47"
                                        + " group by pay.tax_pay_seq"
                                        + ") tax on pay.pay_seq = tax.tax_pay_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.company_tax_account_seq = acc.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"
                                    // เครดิต เงินสด/เงินฝาก
                                    + " SELECT case pay.method_seq"
                                        + " when 1 then ba.cash_account_seq"
                                        + " when 2 then bacc.account_seq"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then bacc3.account_seq"
                                                        + " when '1' then ba.income_bb_account_seq"
                                                        + " end"
                                        + " when 4 then bacc2.account_seq"
                                        + " end as account_seq"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_code"
                                        + " when 2 then acc1.account_code"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_code"
                                                        + " when '1' then acc3.account_code"
                                                        + " end"
                                        + " when 4 then acc4.account_code"
                                        + " end as account_code"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_name"
                                        + " when 2 then acc1.account_name"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_name"
                                                        + " when '1' then acc3.account_name"
                                                        + " end"
                                        + " when 4 then acc4.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.amount as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                        + " and pc.record_status not like 'D'"
                                    + " LEFT JOIN fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq"
                                        + " and pip.record_status not like 'D'"
                                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on pip.bank_acc_seq = bacc2.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc3 on pay.from_bank_acc_id = bacc3.bank_acc_seq"
                                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                                    + " LEFT JOIN acm_mas_account acc4 ON bacc2.account_seq = acc4.account_seq"
                                    + " LEFT JOIN acm_mas_account acc5 ON bacc3.account_seq = acc5.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    ;
                                break;
                            case "26":
                                strSQL = "select ba.revenue_vat_creditor_account_seq as account_seq"
                                    + ", acc.account_code as account_code"
                                    + ", acc.account_name as account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(pay.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " from fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " left join acm_mas_account acc on ba.revenue_vat_creditor_account_seq = acc.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"
                                    // เครดิต เงินสด/เงินฝาก
                                    + " SELECT case pay.method_seq"
                                        + " when 1 then ba.cash_account_seq"
                                        + " when 2 then bacc.account_seq"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then bacc3.account_seq"
                                                        + " when '1' then ba.income_bb_account_seq"
                                                        + " end"
                                        + " when 4 then bacc2.account_seq"
                                        + " end as account_seq"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_code"
                                        + " when 2 then acc1.account_code"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_code"
                                                        + " when '1' then acc3.account_code"
                                                        + " end"
                                        + " when 4 then acc4.account_code"
                                        + " end as account_code"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_name"
                                        + " when 2 then acc1.account_name"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_name"
                                                        + " when '1' then acc3.account_name"
                                                        + " end"
                                        + " when 4 then acc4.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.amount as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                        + " and pc.record_status not like 'D'"
                                    + " LEFT JOIN fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq"
                                        + " and pip.record_status not like 'D'"
                                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on pip.bank_acc_seq = bacc2.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc3 on pay.from_bank_acc_id = bacc3.bank_acc_seq"
                                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                                    + " LEFT JOIN acm_mas_account acc4 ON bacc2.account_seq = acc4.account_seq"
                                    + " LEFT JOIN acm_mas_account acc5 ON bacc3.account_seq = acc5.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    ;
                                break;
                            case "27":
                                strSQL = "SELECT distinct rt.account_seq"
                                    + ", acc1.account_code"
                                    + ", acc1.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(pay.amount, 0) - coalesce(pay.vat, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " inner join fim_receives r on pay.pay_seq = r.pay_seq"
                                    + " inner join fim_mas_receive_types rt on r.receive_type_seq = rt.receive_type_seq"
                                    + " LEFT JOIN acm_mas_account acc1 ON rt.account_seq = acc1.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"

                                    + " SELECT ba.sale_vat_account_seq as account_seq"
                                    + ", acc1.account_code as account_code"
                                    + ", acc1.account_name as account_code"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(pay.vat, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON ba.sale_vat_account_seq = acc1.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    + " and coalesce(pay.vat, 0) > 0"

                                    + " union all"
                                    // เครดิต เงินสด/เงินฝาก
                                    + " SELECT case pay.method_seq"
                                        + " when 1 then ba.cash_account_seq"
                                        + " when 2 then bacc.account_seq"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then bacc3.account_seq"
                                                        + " when '1' then ba.income_bb_account_seq"
                                                        + " end"
                                        + " when 4 then bacc2.account_seq"
                                        + " end as account_seq"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_code"
                                        + " when 2 then acc1.account_code"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_code"
                                                        + " when '1' then acc3.account_code"
                                                        + " end"
                                        + " when 4 then acc4.account_code"
                                        + " end as account_code"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_name"
                                        + " when 2 then acc1.account_name"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_name"
                                                        + " when '1' then acc3.account_name"
                                                        + " end"
                                        + " when 4 then acc4.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.amount as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                        + " and pc.record_status not like 'D'"
                                    + " LEFT JOIN fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq"
                                        + " and pip.record_status not like 'D'"
                                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on pip.bank_acc_seq = bacc2.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc3 on pay.from_bank_acc_id = bacc3.bank_acc_seq"
                                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                                    + " LEFT JOIN acm_mas_account acc4 ON bacc2.account_seq = acc4.account_seq"
                                    + " LEFT JOIN acm_mas_account acc5 ON bacc3.account_seq = acc5.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    ;
                                break;
                            case "28":
                                strSQL = "select ba.payroll_tax_account_seq as account_seq"
                                    + ", acc.account_code as account_code"
                                    + ", acc.account_name as account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(pay.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " from fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " left join acm_mas_account acc on ba.payroll_tax_account_seq = acc.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"
                                    // เครดิต เงินสด/เงินฝาก
                                    + " SELECT case pay.method_seq"
                                        + " when 1 then ba.cash_account_seq"
                                        + " when 2 then bacc.account_seq"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then bacc3.account_seq"
                                                        + " when '1' then ba.income_bb_account_seq"
                                                        + " end"
                                        + " when 4 then bacc2.account_seq"
                                        + " end as account_seq"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_code"
                                        + " when 2 then acc1.account_code"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_code"
                                                        + " when '1' then acc3.account_code"
                                                        + " end"
                                        + " when 4 then acc4.account_code"
                                        + " end as account_code"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_name"
                                        + " when 2 then acc1.account_name"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_name"
                                                        + " when '1' then acc3.account_name"
                                                        + " end"
                                        + " when 4 then acc4.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.amount as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                        + " and pc.record_status not like 'D'"
                                    + " LEFT JOIN fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq"
                                        + " and pip.record_status not like 'D'"
                                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on pip.bank_acc_seq = bacc2.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc3 on pay.from_bank_acc_id = bacc3.bank_acc_seq"
                                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                                    + " LEFT JOIN acm_mas_account acc4 ON bacc2.account_seq = acc4.account_seq"
                                    + " LEFT JOIN acm_mas_account acc5 ON bacc3.account_seq = acc5.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    ;
                                break;
                            default:
                                // ค่าใช้จ่าย
                                strSQL = "SELECT case pay.event_seq when 16 then ba.loan_account_seq"
                                        + " else ba.remain_account_seq"
                                        + " end as account_seq"

                                    + ", case pay.event_seq when 16 then acc1.account_code"
                                        + " else acc2.account_code"
                                        + " end as account_code"

                                    + ", case pay.event_seq when 16 then acc1.account_name"
                                        + " else acc2.account_name"
                                        + " end as account_name"

                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(pay.amount, 0) + coalesce(pay.tax, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON ba.loan_account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.remain_account_seq = acc2.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"
                                    // เครดิต เงินสด/เงินฝาก
                                    + " SELECT case pay.method_seq"
                                        + " when 1 then ba.cash_account_seq"
                                        + " when 2 then bacc.account_seq"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then bacc3.account_seq"
                                                        + " when '1' then ba.income_bb_account_seq"
                                                        + " end"
                                        + " when 4 then bacc2.account_seq"
                                        + " end as account_seq"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_code"
                                        + " when 2 then acc1.account_code"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_code"
                                                        + " when '1' then acc3.account_code"
                                                        + " end"
                                        + " when 4 then acc4.account_code"
                                        + " end as account_code"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_name"
                                        + " when 2 then acc1.account_name"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_name"
                                                        + " when '1' then acc3.account_name"
                                                        + " end"
                                        + " when 4 then acc4.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.amount as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                        + " and pc.record_status not like 'D'"
                                    + " LEFT JOIN fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq"
                                        + " and pip.record_status not like 'D'"
                                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on pip.bank_acc_seq = bacc2.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc3 on pay.from_bank_acc_id = bacc3.bank_acc_seq"
                                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                                    + " LEFT JOIN acm_mas_account acc4 ON bacc2.account_seq = acc4.account_seq"
                                    + " LEFT JOIN acm_mas_account acc5 ON bacc3.account_seq = acc5.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"
                                    // Tax
                                    + " SELECT case when c.ConGrpId is null or c.ConGrpId = 47 then ba.personal_tax_account_seq"
                                        + " else ba.company_tax_account_seq"
                                        + " end as account_seq"
                                    + ", case when c.ConGrpId is null or c.ConGrpId = 47 then acc1.account_code"
                                        + " else acc2.account_code"
                                        + " end as account_code"
                                    + ", case when c.ConGrpId is null or c.ConGrpId = 47 then acc1.account_name"
                                        + " else acc2.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.tax as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN IcmMasContact c ON pay.contact_seq = c.ConId"
                                    + " LEFT JOIN acm_mas_account acc1 ON ba.personal_tax_account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.company_tax_account_seq = acc2.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    ;
                                break;
                        }

                        //if (!chkTaxInvoice)
                        //{
                        strSQL += (strEventSeq != "27" ? " union all"
                            + " SELECT ba.buy_vat_account_seq as account_seq"
                            + ", acc1.account_code as account_code"
                            + ", acc1.account_name as account_code"
                            + ", 'Dr' as dr_or_cr"
                            + ", coalesce(pay.vat, 0) as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_payments pay"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON ba.buy_vat_account_seq = acc1.account_seq"
                            + " WHERE pay.pay_seq = @ref_seq"
                            + " and coalesce(pay.vat, 0) > 0"

                            + " union all"

                            + " SELECT ba.suspense_input_tax_account_seq as account_seq"
                            + ", acc1.account_code as account_code"
                            + ", acc1.account_name as account_code"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", coalesce(pay.vat, 0) as amount_cr"
                            + " FROM fim_payments pay"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON ba.suspense_input_tax_account_seq = acc1.account_seq"
                            + " WHERE pay.pay_seq = @ref_seq"
                            + " and coalesce(pay.vat, 0) > 0" : "");
                        //}

                        break;
                    #endregion
                    #region fim_receives
                    case "fim_receives":
                        // bank or cash
                        strSQL = "SELECT case when r.method_seq = 1 or r.method_seq = 2"
                                + " then cv.cash_account_seq"
                                + " else ba.account_seq"
                                + " end as account_seq"
                            + ", case when r.method_seq = 1 or r.method_seq = 2"
                                + " then acc1.account_code"
                                + " else acc2.account_code"
                                + " end as account_code"
                            + ", case when r.method_seq = 1 or r.method_seq = 2"
                                + " then acc1.account_name"
                                + " else acc2.account_name"
                                + " end as account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", r.amount as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_receives r"
                            + " LEFT JOIN fim_mas_bank_accounts ba ON r.bank_acc_seq = ba.bank_acc_seq"
                            + " cross join acm_mas_base_account cv"
                            + " LEFT JOIN acm_mas_account acc1 ON cv.cash_account_seq = acc1.account_seq"
                            + " LEFT JOIN acm_mas_account acc2 ON ba.account_seq = acc2.account_seq"
                            + " WHERE r.receive_seq = @ref_seq"
                            // tax
                            + " union all"

                            + " SELECT case c.ConGrpId"
                                + " when 47 then cv.personal_tax_account_seq"
                                + " else cv.company_tax_account_seq"
                                + " end as account_seq"
                            + ", case c.ConGrpId"
                                + " when 47 then acc1.account_code"
                                + " else acc2.account_code"
                                + " end as account_code"
                            + ", case c.ConGrpId"
                                + " when 47 then acc1.account_name"
                                + " else acc2.account_name"
                                + " end as account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", r.tax as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_receives r"
                            + " cross join acm_mas_base_account cv"
                            + " LEFT JOIN IcmMasContact c ON r.contact_seq = c.ConId"
                            + " LEFT JOIN acm_mas_account acc1 ON cv.personal_tax_account_seq = acc1.account_seq"
                            + " LEFT JOIN acm_mas_account acc2 ON cv.company_tax_account_seq = acc2.account_seq"
                            + " WHERE r.receive_seq = @ref_seq"
                            // income
                            + " union all"

                            + " SELECT case r.receive_mode"
                                + " when 1 then case when mr.event_seq = 16 then cv.loan_account_seq"
                                    + " else acc5.account_seq"
                                    + " end"
                                + " when 2 then rt.account_seq"
                                + " when 3 then cv.wait_income_bb_account_seq"
                                + " end as account_seq"
                            + ", case r.receive_mode"
                                + " when 1 then case when mr.event_seq = 16 then acc4.account_code"
                                    + " else acc5.account_code"
                                    + " end"
                                + " when 2 then acc2.account_code"
                                + " when 3 then acc3.account_code"
                                + " end as account_code"
                            + ", case r.receive_mode"
                                + " when 1 then case when mr.event_seq = 16 then acc4.account_name"
                                    + " else acc5.account_name"
                                    + " end"
                                + " when 2 then acc2.account_name"
                                + " when 3 then acc3.account_name"
                                + " end as account_name"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", r.amount - coalesce(r.vat_amount, 0) as amount_cr"
                            + " FROM fim_receives r"
                            + " LEFT JOIN (select mr.money_req_seq, mr.event_seq, max(mrd.bgm_proj_oper_exp_seq) as bgm_proj_oper_exp_seq"
	                            + " from fim_money_requests mr"
	                            + " LEFT JOIN fim_money_request_dtls mrd on mrd.money_req_seq = mr.money_req_seq"
	                            + " where mrd.record_status <> 'D'"
	                            + " GROUP BY mr.money_req_seq, mr.event_seq"
	                            + ") mr on r.money_req_seq = mr.money_req_seq"
                            + " left join bgm_project_operation_expense ope on mr.bgm_proj_oper_exp_seq = ope.bgm_proj_oper_exp_seq"
                            + " left join bgm_project_expense pje on ope.bgm_proj_exp_seq = pje.bgm_proj_exp_seq"
                            + " left join bgm_mas_expense exp on pje.bgm_exp_seq = exp.bgm_exp_seq"
                            + " cross join acm_mas_base_account cv"
                            + " LEFT JOIN fim_mas_receive_types rt on r.receive_type_seq = rt.receive_type_seq"
                            + " LEFT JOIN acm_mas_account acc1 ON cv.over_account_seq = acc1.account_seq"
                            + " LEFT JOIN acm_mas_account acc2 ON rt.account_seq = acc2.account_seq"
                            + " LEFT JOIN acm_mas_account acc3 ON cv.wait_income_bb_account_seq = acc3.account_seq"
                            + " LEFT JOIN acm_mas_account acc4 ON cv.loan_account_seq = acc4.account_seq"
                            + " LEFT JOIN acm_mas_account acc5 ON exp.account_seq = acc5.account_seq"
                            + " WHERE r.receive_seq = @ref_seq"
                            // tax
                            + " union all"

                            + " SELECT acc1.account_seq as account_seq"
                            + ", acc1.account_code as account_code"
                            + ", acc1.account_name as account_name"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", r.vat_amount as amount_cr"
                            + " FROM fim_receives r"
                            + " cross join acm_mas_base_account cv"
                            + " LEFT JOIN acm_mas_account acc1 ON cv.sale_vat_account_seq = acc1.account_seq"
                            + " WHERE r.receive_seq = @ref_seq";
                        break;
                    #endregion
                    #region fim_money_returns
                    case "fim_money_returns":
                        strSQL = "SELECT exp.account_seq"
                            + ", acc1.account_code"
                            + ", acc1.account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", sum(coalesce(retd.amount, 0) - coalesce(retd.vat_amount, 0)) as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_money_returns ret"
                            + " LEFT JOIN fim_money_return_dtls retd on ret.return_seq = retd.return_seq"
                                + " and retd.record_status not like 'D'"
                            + " LEFT JOIN fim_money_request_dtls mrd on retd.money_req_dtls_seq = mrd.money_req_dtls_seq"
                            + " LEFT JOIN bgm_project_operation_expense opexp on mrd.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                            + " LEFT JOIN bgm_project_expense pjexp on opexp.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
                            + " LEFT JOIN bgm_mas_expense exp on pjexp.bgm_exp_seq = exp.bgm_exp_seq"
                            + " LEFT JOIN acm_mas_account acc1 ON exp.account_seq = acc1.account_seq"
                            + " WHERE ret.return_seq = @ref_seq"
                            + " group by exp.account_seq, acc1.account_code, acc1.account_name"

                            + " union all"

                            + " SELECT acc1.account_seq"
                            + ", acc1.account_code"
                            + ", acc1.account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", sum(coalesce(retd.vat_amount, 0)) as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_money_returns ret"
                            + " LEFT JOIN fim_money_return_dtls retd on ret.return_seq = retd.return_seq"
                                + " and retd.record_status not like 'D'"
                            + " cross JOIN acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON ba.buy_vat_account_seq = acc1.account_seq"
                            + " WHERE ret.return_seq = @ref_seq"
                            + " group by acc1.account_seq, acc1.account_code, acc1.account_name"

                            + " union all"

                            + " SELECT ba.loan_account_seq as account_seq"
                            + ", acc1.account_code"
                            + ", acc1.account_name"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", retd.amount as amount_cr"
                            + " FROM fim_money_returns ret"
                            + " LEFT JOIN (select retd.return_seq"
                                + ", sum(coalesce(retd.amount, 0)) as amount"
                                + " from fim_money_return_dtls retd"
                                + " where retd.record_status not like 'D'"
                                + " group by retd.return_seq"
                                + ") retd on ret.return_seq = retd.return_seq"
                            + " CROSS JOIN acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON ba.loan_account_seq = acc1.account_seq"
                            + " WHERE ret.return_seq = @ref_seq";
                        break;
                    #endregion
                    #region icm_delivery_note
                    case "icm_delivery_note":
                        DataTable dtbQuotation = GetQuotationData(ref_seq.ToString());

                        for (int i = 0; i < dtbQuotation.Rows.Count; i++)
                        {
                            string BidderQuoId = dtbQuotation.Rows[i]["BidderQuoId"].ToString();

                            //จำนวนงวดที่เป็นงานระหว่างทำ
                            int nRowCount = GetHaveItemRowCount(BidderQuoId);

                            // ถ้าไม่เป็นงานระหว่างทำ แต่มีงานระหว่างทำในงวดอื่นๆ
                            if (dtbQuotation.Rows[i]["HaveItem"].ToString() == "1" && nRowCount > 0)
                            {
                                //ลงงานระหว่างทำก่อน
                                DataTable dtbDni = GetDnItemData(ref_seq.ToString(), BidderQuoId);
                                AddDnItemToList(dtbDni, infoList);

                                //จากนั้นเปลี่ยนงานระหว่างทำเป็นสินทรัพย์
                                DataTable dtbDni2 = GetDnItemData2(BidderQuoId);
                                AddDnItemToList2(dtbDni2, infoList);
                            }
                            else
                            {
                                //ถ้าเป็นงานระหว่างทำ หรือไม่มีงานระหว่างทำในงวดอื่นๆ
                                dbManager.CreateParameters(2);
                                dbManager.AddParameters(0, "@BidderQuoId", dtbQuotation.Rows[i]["BidderQuoId"].ToString());
                                dbManager.AddParameters(1, "@DnId", ref_seq);

                                strSQL = @"
select account_seq1, account_code1, account_name1
, account_seq3
, account_code3
, account_name3
, account_seq4
, account_code4
, account_name4
, account_seq5
, account_code5
, account_name5
, HaveItem
, sum(coalesce(amount, 0)) as amount
, sum(coalesce(amount_vat, 0)) as amount_vat
from (SELECT case when ri.ResItemId is not null and rg.ResTypeId <> 1
	then rg.AssetAccountId
	else ex.account_seq
	end as account_seq1
, case when ri.ResItemId is not null and rg.ResTypeId <> 1
	then acc1.account_code
	else acc2.account_code
	end as account_code1
, case when ri.ResItemId is not null and rg.ResTypeId <> 1
	then acc1.account_name
	else acc2.account_name
	end as account_name1
, acc3.account_seq as account_seq3
, acc3.account_code as account_code3
, acc3.account_name as account_name3
, acc4.account_seq as account_seq4
, acc4.account_code as account_code4
, acc4.account_name as account_name4
, acc5.account_seq as account_seq5
, acc5.account_code as account_code5
, acc5.account_name as account_name5
, coalesce(ni.NetAmount, 0) as amount
, COALESCE(ni.VatAmount, 0) as amount_vat
, ni.HaveItem

FROM IcmDeliveryNoteItem ni
left join IcmPurchaseOrderInstallmentItem ii on ni.PoInstallItemId = ii.PoInstallItemId
left join IcmBidderQuotation q on ii.BidderQuoId = q.BidderQuoId
left join IcmPurchaseRequestItem ri on q.PrItemId = ri.PrItemId
left join bgm_mas_expense ex on ri.BgExpId = ex.bgm_exp_seq
left join IcmMasResourceItem mi on ri.ResItemId = mi.ResItemId
left join IcmMasResourceSubGroup sg on mi.ResSubGrpId = sg.ResSubGrpId
left join IcmMasResourceGroup rg on sg.ResGrpId = rg.ResGrpId
LEFT JOIN acm_mas_account acc1 ON rg.AssetAccountId = acc1.account_seq
LEFT JOIN acm_mas_account acc2 ON ex.account_seq = acc2.account_seq
cross join acm_mas_base_account ba
LEFT JOIN acm_mas_account acc3 ON ba.suspense_input_tax_account_seq = acc3.account_seq
LEFT JOIN acm_mas_account acc4 ON ba.direct_pay_account_seq = acc4.account_seq
LEFT JOIN acm_mas_account acc5 ON ba.wait_account_seq = acc5.account_seq
WHERE ni.DnId = @DnId
and ii.BidderQuoId = @BidderQuoId
) t
group by account_seq1, account_code1, account_name1
, account_seq3
, account_code3
, account_name3
, account_seq4
, account_code4
, account_name4
, account_seq5
, account_code5
, account_name5
, HaveItem";
                                DataTable dtbDNDtls = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                                double dAmount = 0;

                                foreach (DataRow dr in dtbDNDtls.Rows)
                                {
                                    if (dr["HaveItem"].ToString() == "False")
                                    {
                                        M_ACM_GENERATE_ACCOUNT_Auto info1 = new M_ACM_GENERATE_ACCOUNT_Auto()
                                        {
                                            account_seq = cn.ConvertNullInt(dr["account_seq5"]),
                                            dr_or_cr = "Dr",
                                            account_code = dr["account_code5"].ToString(),
                                            account_name = dr["account_name5"].ToString(),
                                            amount_dr = cn.ConvertNullDouble(dr["amount"]) - cn.ConvertNullDouble(dr["amount_vat"]),
                                            amount_cr = 0,
                                        };
                                        infoList.Add(info1);
                                    }
                                    else
                                    {
                                        M_ACM_GENERATE_ACCOUNT_Auto info1 = new M_ACM_GENERATE_ACCOUNT_Auto()
                                        {
                                            account_seq = cn.ConvertNullInt(dr["account_seq1"]),
                                            dr_or_cr = "Dr",
                                            account_code = dr["account_code1"].ToString(),
                                            account_name = dr["account_name1"].ToString(),
                                            amount_dr = cn.ConvertNullDouble(dr["amount"]) - cn.ConvertNullDouble(dr["amount_vat"]),
                                            amount_cr = 0,
                                        };
                                        infoList.Add(info1);
                                    }

                                    if (cn.ConvertNullDouble(dr["amount_vat"]) != 0)
                                    {
                                        M_ACM_GENERATE_ACCOUNT_Auto info2 = new M_ACM_GENERATE_ACCOUNT_Auto()
                                        {
                                            account_seq = cn.ConvertNullInt(dr["account_seq3"]),
                                            dr_or_cr = "Dr",
                                            account_code = dr["account_code3"].ToString(),
                                            account_name = dr["account_name3"].ToString(),
                                            amount_dr = cn.ConvertNullDouble(dr["amount_vat"]),
                                            amount_cr = 0,
                                        };

                                        infoList.Add(info2);
                                    }

                                    dAmount += cn.ConvertNullDouble(dr["amount_vat"]) ?? 0;
                                }
                            }
                        }

                        DataTable dtbPenalty = GetPenaltyAmount(ref_seq.ToString());
                        double PenAmount = dtbPenalty.Rows.Count > 0 ? (cn.ConvertNullDouble(dtbPenalty.Rows[0]["PenaltyFinalAmount"]) ?? 0) : 0;

                        M_ACM_GENERATE_ACCOUNT_Auto info3 = new M_ACM_GENERATE_ACCOUNT_Auto()
                        {
                            account_seq = cn.ConvertNullInt(dtbPenalty.Rows[0]["account_seq2"]),
                            dr_or_cr = "Cr",
                            account_code = dtbPenalty.Rows[0]["account_code2"].ToString(),
                            account_name = dtbPenalty.Rows[0]["account_name2"].ToString(),
                            amount_dr = 0,
                            amount_cr = (cn.ConvertNullDouble(dtbPenalty.Rows[0]["NetAmount"]) ?? 0) - PenAmount,
                        };

                        infoList.Add(info3);

                        if (PenAmount > 0)
                        {
                            M_ACM_GENERATE_ACCOUNT_Auto info4 = new M_ACM_GENERATE_ACCOUNT_Auto()
                            {
                                account_seq = cn.ConvertNullInt(dtbPenalty.Rows[0]["account_seq1"]),
                                dr_or_cr = "Cr",
                                account_code = dtbPenalty.Rows[0]["account_code1"].ToString(),
                                account_name = dtbPenalty.Rows[0]["account_name1"].ToString(),
                                amount_dr = 0,
                                amount_cr = PenAmount,
                            };

                            infoList.Add(info4);
                        }

                        return infoList;
                        break;
                    #endregion
                    #region fim_transfers
                    case "fim_transfers":
                        strSQL = "select facc.bgm_source_seq as from_bgm_source_seq"
                            + ", tacc.bgm_source_seq as to_bgm_source_seq"
                            + " from fim_transfers tf"
                            + " left join fim_mas_bank_accounts facc on tf.from_bank_acc_seq = facc.bank_acc_seq"
                            + " left join fim_mas_bank_accounts tacc on tf.to_bank_acc_seq = tacc.bank_acc_seq"
                            + " where tf.transfer_seq = " + ref_seq;
                        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                        if (dtb.Rows.Count > 0)
                        {
                            if (!string.IsNullOrEmpty(dtb.Rows[0][0].ToString()) && !string.IsNullOrEmpty(dtb.Rows[0][1].ToString()) && dtb.Rows[0][0].ToString() != dtb.Rows[0][1].ToString())
                            {
                                strSQL = "SELECT ba.cross_budget_expense_account_seq as account_seq"
                                    + ", acc.account_code"
                                    + ", acc.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(t.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_transfers t"
                                    + " CROSS JOIN acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.cross_budget_expense_account_seq = acc.account_seq"

                                    + " WHERE t.transfer_seq = @ref_seq"

                                    + " union all"

                                    + " SELECT bacc1.account_seq as account_seq"
                                    + ", acc1.account_code"
                                    + ", acc1.account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", coalesce(t.amount, 0) as amount_cr"
                                    + " FROM fim_transfers t"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc1 on t.from_bank_acc_seq = bacc1.bank_acc_seq"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc1.account_seq = acc1.account_seq"

                                    + " WHERE t.transfer_seq = @ref_seq";
                            }
                            else
                            {
                                strSQL = "SELECT case t.transfer_type"
                                        + " when '1' then acc2.account_seq"
                                        + " when '2' then acc3.account_seq"
                                        + " when '3' then acc2.account_seq"
                                        + " else 0"
                                        + " end as account_seq"
                                    + ", case t.transfer_type"
                                        + " when '1' then acc2.account_code"
                                        + " when '2' then acc3.account_code"
                                        + " when '3' then acc2.account_code"
                                        + " else ''"
                                        + " end as account_code"
                                    + ", case t.transfer_type"
                                        + " when '1' then acc2.account_name"
                                        + " when '2' then acc3.account_name"
                                        + " when '3' then acc2.account_name"
                                        + " else ''"
                                        + " end as account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(t.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_transfers t"
                                    + " CROSS JOIN acm_mas_base_account ba"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc1 on t.from_bank_acc_seq = bacc1.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on t.to_bank_acc_seq = bacc2.bank_acc_seq"

                                    + " LEFT JOIN acm_mas_account acc1 ON bacc1.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON bacc2.account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.cash_account_seq = acc3.account_seq"

                                    + " WHERE t.transfer_seq = @ref_seq"

                                    + " union all"

                                    + " SELECT case t.transfer_type"
                                        + " when '1' then acc3.account_seq"
                                        + " when '2' then acc1.account_seq"
                                        + " when '3' then acc1.account_seq"
                                        + " else 0"
                                        + " end as account_seq"
                                    + ", case t.transfer_type"
                                        + " when '1' then acc3.account_code"
                                        + " when '2' then acc1.account_code"
                                        + " when '3' then acc1.account_code"
                                        + " else ''"
                                        + " end as account_code"
                                    + ", case t.transfer_type"
                                        + " when '1' then acc3.account_name"
                                        + " when '2' then acc1.account_name"
                                        + " when '3' then acc1.account_name"
                                        + " else ''"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", coalesce(t.amount, 0) as amount_cr"
                                    + " FROM fim_transfers t"
                                    + " CROSS JOIN acm_mas_base_account ba"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc1 on t.from_bank_acc_seq = bacc1.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on t.to_bank_acc_seq = bacc2.bank_acc_seq"

                                    + " LEFT JOIN acm_mas_account acc1 ON bacc1.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON bacc2.account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.cash_account_seq = acc3.account_seq"

                                    + " WHERE t.transfer_seq = @ref_seq";
                            }
                        }
                        break;
                    #endregion
                    #region fim_cheques
                    case "fim_cheques":
                        strSQL = "SELECT acc.account_seq as account_seq"
                            + ", acc.account_code as account_code"
                            + ", acc.account_name as account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", coalesce(pc.amount, 0) as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_cheques c"
                            + " LEFT JOIN fim_mas_bank_accounts bacc on c.bank_acc_seq = bacc.bank_acc_seq"
                            + " LEFT JOIN acm_mas_account acc ON bacc.account_seq = acc.account_seq"
                            + " left join (select pc.cheque_seq"
                                + ", sum(coalesce(pc.amount, 0)) as amount"
                                + " from fim_payment_cheques pc"
                                + " inner join fim_payments p on pc.pay_seq = p.pay_seq"
                                    + " and p.record_status = 'A'"
                                + " where pc.record_status not like 'D'"
                                + " group by pc.cheque_seq"
                                + ") pc on c.cheque_seq = pc.cheque_seq"
                            + " WHERE c.cheque_seq = @ref_seq"

                            + " union all"

                            + " SELECT acc.account_seq as account_seq"
                            + ", acc.account_code as account_code"
                            + ", acc.account_name as account_name"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", coalesce(pc.amount, 0) as amount_cr"
                            + " FROM fim_cheques c"
                            + " LEFT JOIN fim_mas_bank_accounts bacc on c.bank_acc_seq = bacc.bank_acc_seq"
                            + " LEFT JOIN fim_mas_bank_accounts bacc1 on bacc.saving_bank_acc_seq = bacc1.bank_acc_seq"
                            + " LEFT JOIN acm_mas_account acc ON bacc1.account_seq = acc.account_seq"
                            + " left join (select pc.cheque_seq"
                                + ", sum(coalesce(pc.amount, 0)) as amount"
                                + " from fim_payment_cheques pc"
                                + " inner join fim_payments p on pc.pay_seq = p.pay_seq"
                                    + " and p.record_status = 'A'"
                                + " where pc.record_status not like 'D'"
                                + " group by pc.cheque_seq"
                                + ") pc on c.cheque_seq = pc.cheque_seq"
                            + " WHERE c.cheque_seq = @ref_seq";
                        break;
                    #endregion
                    #region fim_vat
                    case "fim_vat":
                        strSQL = @"
select ba.sale_vat_account_seq as account_seq
, acc.account_code as account_code
, acc.account_name as account_name
, 'Dr' as dr_or_cr
, coalesce(v.vat_sale_amount, 0) as amount_dr
, null as amount_cr
from fim_vat v
cross join acm_mas_base_account ba
left join acm_mas_account acc on ba.sale_vat_account_seq = acc.account_seq
where v.vat_seq = @ref_seq

union all

select ba.revenue_vat_debtor_account_seq as account_seq
, acc.account_code as account_code
, acc.account_name as account_name
, 'Dr' as dr_or_cr
, coalesce(v.vat_buy_amount, 0) - coalesce(v.vat_sale_amount, 0) as amount_dr
, null as amount_cr
from fim_vat v
cross join acm_mas_base_account ba
left join acm_mas_account acc on ba.revenue_vat_debtor_account_seq = acc.account_seq
where coalesce(v.vat_buy_amount, 0) - coalesce(v.vat_sale_amount, 0) > 0
and v.vat_seq = @ref_seq

union all

select ba.buy_vat_account_seq as account_seq
, acc.account_code as account_code
, acc.account_name as account_name
, 'Cr' as dr_or_cr
, null as amount_dr
, coalesce(v.vat_buy_amount, 0) as amount_cr
from fim_vat v
cross join acm_mas_base_account ba
left join acm_mas_account acc on ba.buy_vat_account_seq = acc.account_seq
where v.vat_seq = @ref_seq

union all

select ba.revenue_vat_creditor_account_seq as account_seq
, acc.account_code as account_code
, acc.account_name as account_name
, 'Cr' as dr_or_cr
, null as amount_dr
, coalesce(v.vat_sale_amount, 0) - coalesce(v.vat_buy_amount, 0) as amount_cr
from fim_vat v
cross join acm_mas_base_account ba
left join acm_mas_account acc on ba.revenue_vat_creditor_account_seq = acc.account_seq
where coalesce(v.vat_sale_amount, 0) - coalesce(v.vat_buy_amount, 0) > 0
and v.vat_seq = @ref_seq
";
                        break;
                    #endregion
                }

                if (strSQL.Length > 0)
                {
                    dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                    if (dtb.Rows.Count > 0)
                    {
                        for (int i = 0; i < dtb.Rows.Count; i++)
                        {
                            DataRow dr = dtb.Rows[i];

                            if ((cn.ConvertNullInt(dr["amount_dr"]) != null && cn.ConvertNullInt(dr["amount_dr"]) != 0 && dr["dr_or_cr"].ToString() == "Dr")
                                || (cn.ConvertNullInt(dr["amount_cr"]) != null && cn.ConvertNullInt(dr["amount_cr"]) != 0 && dr["dr_or_cr"].ToString() == "Cr"))
                            {
                                M_ACM_GENERATE_ACCOUNT_Auto info = new M_ACM_GENERATE_ACCOUNT_Auto();
                                info.account_seq = cn.ConvertNullInt(dr["account_seq"]);
                                info.dr_or_cr = dr["dr_or_cr"].ToString();
                                info.account_code = dr["account_code"].ToString();
                                info.account_name = dr["account_name"].ToString();
                                info.amount_dr = cn.ConvertNullDouble(dr["amount_dr"]);
                                info.amount_cr = cn.ConvertNullDouble(dr["amount_cr"]);
                                infoList.Add(info);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        //public IList<M_ACM_GENERATE_ACCOUNT_Auto> GetListDetail(Nullable<Int32> ref_seq, string ref_type)
        //{
        //    DataRowCollection drc = null;
        //    dbManager.CreateParameters(1);
        //    dbManager.AddParameters(0, "@ref_seq", ref_seq);
        //    IList<M_ACM_GENERATE_ACCOUNT_Auto> infoList = new List<M_ACM_GENERATE_ACCOUNT_Auto>();
        //    try
        //    {
        //        dbManager.Open();
        //        string strSQL = "";
        //        string strEventSeq = "";

        //        switch (ref_type)
        //        {
        //            case "fim_petitions":
        //                strSQL = "SELECT ba.wait_income_bb_account_seq as account_seq"
        //                    + ", acc.account_code"
        //                    + ", acc.account_name"
        //                    + ", 'Dr' as dr_or_cr"
        //                    + ", ptd.amount as amount_dr"
        //                    + ", null as amount_cr"
        //                    + " FROM fim_petitions pt"
        //                    + " LEFT JOIN (select petition_seq"
        //                        + " , sum(coalesce(amount_real, 0)) as amount"
        //                        + " from fim_petition_dtls"
        //                        + " where record_status not like 'D'"
        //                        + " group by petition_seq"
        //                        + " ) ptd ON pt.petition_seq = ptd.petition_seq"
        //                    + " cross join acm_mas_base_account ba"
        //                    + " LEFT JOIN acm_mas_account acc ON ba.wait_income_bb_account_seq = acc.account_seq"
        //                    + " WHERE pt.petition_seq = @ref_seq"

        //                    + " union all"

        //                    + " SELECT ba.income_bb_account_seq as account_seq"
        //                    + ", acc.account_code"
        //                    + ", acc.account_name"
        //                    + ", 'Cr' as dr_or_cr"
        //                    + ", null as amount_dr"
        //                    + ", ptd.amount as amount_cr"
        //                    + " FROM fim_petitions pt"
        //                    + " LEFT JOIN (select petition_seq"
        //                        + " , sum(coalesce(amount_real, 0)) as amount"
        //                        + " from fim_petition_dtls"
        //                        + " where record_status not like 'D'"
        //                        + " group by petition_seq"
        //                        + " ) ptd ON pt.petition_seq = ptd.petition_seq"
        //                    + " cross join acm_mas_base_account ba"
        //                    + " LEFT JOIN acm_mas_account acc ON ba.income_bb_account_seq = acc.account_seq"
        //                    + " WHERE pt.petition_seq = @ref_seq";
        //                break;
        //            case "fim_money_requests":
        //                strSQL = "SELECT event_seq"
        //                    + " FROM fim_money_requests"
        //                    + " WHERE money_req_seq = @ref_seq";
        //                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

        //                if (drc.Count > 0)
        //                {
        //                    strEventSeq = drc[0]["event_seq"].ToString();
        //                }

        //                switch (strEventSeq)
        //                {
        //                    case "11":
        //                        strSQL = "SELECT ba.direct_pay_account_seq as account_seq"
        //                            + ", acc.account_code, acc.account_name"
        //                            + ", 'Dr' as dr_or_cr"
        //                            + ", mrd.amount as amount_dr"
        //                            + ", null as amount_cr"
        //                            + " FROM fim_money_requests mr"
        //                            + " LEFT JOIN (select mrd.money_req_seq"
        //                                + ", sum(coalesce(mrd.amount, 0)) as amount"
        //                                + " from fim_money_request_dtls mrd"
        //                                + " where mrd.record_status not like 'D'"
        //                                + " group by mrd.money_req_seq"
        //                                + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
        //                            + " cross join acm_mas_base_account ba"
        //                            + " LEFT JOIN acm_mas_account acc ON ba.direct_pay_account_seq = acc.account_seq"
        //                            + " WHERE mr.money_req_seq = @ref_seq"

        //                            + " UNION ALL"

        //                            + " SELECT ba.dn_cr_account_seq as account_seq"
        //                            + ", acc.account_code, acc.account_name"
        //                            + ", 'Cr' as dr_or_cr"
        //                            + ", null as amount_dr"
        //                            + ", mrd.amount as amount_cr"
        //                            + " FROM fim_money_requests mr"
        //                            + " LEFT JOIN (select mrd.money_req_seq"
        //                                + ", sum(coalesce(mrd.amount, 0)) as amount"
        //                                + " from fim_money_request_dtls mrd"
        //                                + " where mrd.record_status not like 'D'"
        //                                + " group by mrd.money_req_seq"
        //                                + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
        //                            + " cross join acm_mas_base_account ba"
        //                            + " LEFT JOIN acm_mas_account acc ON ba.dn_cr_account_seq = acc.account_seq"
        //                            + " WHERE mr.money_req_seq = @ref_seq";
        //                        break;
        //                    case "12":
        //                    case "14":
        //                    case "15":
        //                    case "17":
        //                    case "18":
        //                        strSQL = "SELECT exp.account_seq as account_seq"
        //                            + ", acc.account_code, acc.account_name"
        //                            + ", 'Dr' as dr_or_cr"
        //                            + ", mrd.amount as amount_dr"
        //                            + ", null as amount_cr"
        //                            //+ ", j.job_site_gfmis as cost_center"
        //                            + " FROM fim_money_requests mr"
        //                            + " LEFT JOIN fim_money_request_dtls mrd ON mr.money_req_seq = mrd.money_req_seq"
        //                            + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
        //                            + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
        //                            + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
        //                            //+ " LEFT JOIN hrm_mp_job_sites j ON mrd.job_site_seq = j.job_site_seq"
        //                            + " LEFT JOIN acm_mas_account acc ON exp.account_seq = acc.account_seq"
        //                            + " WHERE mr.money_req_seq = @ref_seq"
        //                            + " AND mrd.record_status not like 'D'"

        //                            + " UNION ALL"

        //                            + " SELECT ba.remain_account_seq as account_seq"
        //                            + ", acc.account_code, acc.account_name"
        //                            + ", 'Cr' as dr_or_cr"
        //                            + ", null as amount_dr"
        //                            + ", mrd.amount as amount_cr"
        //                            + " FROM fim_money_requests mr"
        //                            + " LEFT JOIN (select mrd.money_req_seq"
        //                                + ", sum(coalesce(mrd.amount, 0)) as amount"
        //                                + " from fim_money_request_dtls mrd"
        //                                + " where mrd.record_status not like 'D'"
        //                                + " group by mrd.money_req_seq"
        //                                + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
        //                            + " cross join acm_mas_base_account ba"
        //                            + " LEFT JOIN acm_mas_account acc ON ba.remain_account_seq = acc.account_seq"
        //                            + " WHERE mr.money_req_seq = @ref_seq";
        //                        break;
        //                }
        //                break;
        //            case "fim_payments":
        //                // ค่าใช้จ่าย
        //                strSQL = "SELECT case pay.event_seq"
        //                        + " when 16 then ba.loan_account_seq"
        //                        + " when 18 then ba.loan_account_seq"
        //                        + " when 11 then ba.dn_cr_account_seq"
        //                        + " else ba.remain_account_seq"
        //                        + " end as account_seq"
        //                    + ", case pay.event_seq"
        //                        + " when 16 then acc1.account_code"
        //                        + " when 18 then acc1.account_code"
        //                        + " when 11 then acc2.account_code"
        //                        + " else acc3.account_code"
        //                        + " end as account_code"
        //                    + ", case pay.event_seq"
        //                        + " when 16 then acc1.account_name"
        //                        + " when 18 then acc1.account_name"
        //                        + " when 11 then acc2.account_name"
        //                        + " else acc3.account_name"
        //                        + " end as account_name"
        //                    + ", 'Dr' as dr_or_cr"
        //                    + ", coalesce(pay.amount, 0) - coalesce(pay.vat, 0) as amount_dr"
        //                    + ", null as amount_cr"
        //                    + " FROM fim_payments pay"
        //                    //+ " LEFT JOIN (select mrd.money_req_seq"
        //                    //    + ", sum(coalesce(mrd.amount, 0)) as amount"
        //                    //    + " from fim_money_request_dtls mrd"
        //                    //    + " where record_status not like 'D'"
        //                    //    + " group by mrd.money_req_seq"
        //                    //    + ") mrd on pay.money_req_seq = mrd.money_req_seq"
        //                    + " cross join acm_mas_base_account ba"
        //                    + " LEFT JOIN acm_mas_account acc1 ON ba.loan_account_seq = acc1.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc2 ON ba.remain_account_seq = acc2.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc3 ON ba.dn_cr_account_seq = acc3.account_seq"
        //                    + " WHERE pay.pay_seq = @ref_seq"

        //                    + " union all"
        //                    // ภาษีซื้อ
        //                    + " SELECT ba.buy_vat_account_seq as account_seq"
        //                    + ", acc1.account_code as account_code"
        //                    + ", acc1.account_name as account_name"
        //                    + ", 'Dr' as dr_or_cr"
        //                    + ", pay.vat as amount_dr"
        //                    + ", null as amount_cr"
        //                    + " FROM fim_payments pay"
        //                    + " cross join acm_mas_base_account ba"
        //                    + " LEFT JOIN acm_mas_account acc1 ON ba.buy_vat_account_seq = acc1.account_seq"
        //                    + " WHERE pay.pay_seq = @ref_seq"

        //                    + " union all"
        //                    // เครดิต เงินสด/เงินฝาก
        //                    + " SELECT case pay.method_seq"
        //                        + " when 1 then ba.cash_account_seq"
        //                        + " when 2 then bacc.account_seq"
        //                        + " when 3 then case coalesce(pet.direct_pay, '0')"
        //                                        + " when '0' then bacc.account_seq"
        //                                        + " when '1' then ba.income_bb_account_seq"
        //                                        + " end"
        //                        + " end as account_seq"
        //                    + ", case pay.method_seq"
        //                        + " when 1 then acc2.account_code"
        //                        + " when 2 then acc1.account_code"
        //                        + " when 3 then case coalesce(pet.direct_pay, '0')"
        //                                        + " when '0' then acc1.account_code"
        //                                        + " when '1' then acc3.account_code"
        //                                        + " end"
        //                        + " end as account_code"
        //                    + ", case pay.method_seq"
        //                        + " when 1 then acc2.account_name"
        //                        + " when 2 then acc1.account_name"
        //                        + " when 3 then case coalesce(pet.direct_pay, '0')"
        //                                        + " when '0' then acc1.account_name"
        //                                        + " when '1' then acc3.account_name"
        //                                        + " end"
        //                        + " end as account_name"
        //                    + ", 'Cr' as dr_or_cr"
        //                    + ", null as amount_dr"
        //                    + ", pay.amount as amount_cr"
        //                    + " FROM fim_payments pay"
        //                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
        //                        + " and pc.record_status not like 'D'"
        //                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
        //                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
        //                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
        //                    + " cross join acm_mas_base_account ba"
        //                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
        //                    + " WHERE pay.pay_seq = @ref_seq"

        //                    + " union all"
        //                    // Tax
        //                    + " SELECT case c.ConGrpId"
        //                        + " when 47 then ba.personal_tax_account_seq"
        //                        + " else ba.company_tax_account_seq"
        //                        + " end as account_seq"
        //                    + ", case c.ConGrpId"
        //                        + " when 47 then acc1.account_code"
        //                        + " else acc2.account_code"
        //                        + " end as account_code"
        //                    + ", case c.ConGrpId"
        //                        + " when 47 then acc1.account_name"
        //                        + " else acc2.account_name"
        //                        + " end as account_name"
        //                    + ", 'Cr' as dr_or_cr"
        //                    + ", null as amount_dr"
        //                    + ", pay.tax as amount_cr"
        //                    + " FROM fim_payments pay"
        //                    + " cross join acm_mas_base_account ba"
        //                    + " LEFT JOIN IcmMasContact c ON pay.contact_seq = c.ConId"
        //                    + " LEFT JOIN acm_mas_account acc1 ON ba.company_tax_account_seq = acc1.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc2 ON ba.personal_tax_account_seq = acc2.account_seq"
        //                    + " WHERE pay.pay_seq = @ref_seq"
        //                    ;
        //                break;
        //            case "fim_receives":
        //                // bank or cash
        //                strSQL = "SELECT case when r.method_seq = 1 or r.method_seq = 2"
        //                        + " then cv.cash_account_seq"
        //                        + " else ba.account_seq"
        //                        + " end as account_seq"
        //                    + ", case when r.method_seq = 1 or r.method_seq = 2"
        //                        + " then acc1.account_code"
        //                        + " else acc2.account_code"
        //                        + " end as account_code"
        //                    + ", case when r.method_seq = 1 or r.method_seq = 2"
        //                        + " then acc1.account_name"
        //                        + " else acc2.account_name"
        //                        + " end as account_name"
        //                    + ", 'Dr' as dr_or_cr"
        //                    + ", r.amount as amount_dr"
        //                    + ", null as amount_cr"
        //                    + " FROM fim_receives r"
        //                    + " LEFT JOIN fim_mas_bank_accounts ba ON r.bank_acc_seq = ba.bank_acc_seq"
        //                    + " cross join acm_mas_base_account cv"
        //                    + " LEFT JOIN acm_mas_account acc1 ON cv.cash_account_seq = acc1.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc2 ON ba.account_seq = acc2.account_seq"
        //                    + " WHERE r.receive_seq = @ref_seq"
        //                    // tax
        //                    + " union all"

        //                    + " SELECT case c.ConGrpId"
        //                        + " when 47 then cv.personal_tax_account_seq"
        //                        + " else cv.company_tax_account_seq"
        //                        + " end as account_seq"
        //                    + ", case c.ConGrpId"
        //                        + " when 47 then acc1.account_code"
        //                        + " else acc2.account_code"
        //                        + " end as account_code"
        //                    + ", case c.ConGrpId"
        //                        + " when 47 then acc1.account_name"
        //                        + " else acc2.account_name"
        //                        + " end as account_name"
        //                    + ", 'Dr' as dr_or_cr"
        //                    + ", r.tax as amount_dr"
        //                    + ", null as amount_cr"
        //                    + " FROM fim_receives r"
        //                    + " cross join acm_mas_base_account cv"
        //                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c.ConId"
        //                    + " LEFT JOIN acm_mas_account acc1 ON cv.company_tax_account_seq = acc1.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc2 ON cv.personal_tax_account_seq = acc2.account_seq"
        //                    + " WHERE r.receive_seq = @ref_seq"
        //                    // income
        //                    + " union all"

        //                    + " SELECT case r.receive_mode"
        //                        + " when 1 then cv.over_account_seq"
        //                        + " when 2 then rt.account_seq"
        //                        + " when 3 then cv.wait_income_bb_account_seq"
        //                        + " end as account_seq"
        //                    + ", case r.receive_mode"
        //                        + " when 1 then acc1.account_code"
        //                        + " when 2 then acc2.account_code"
        //                        + " when 3 then acc3.account_code"
        //                        + " end as account_code"
        //                    + ", case r.receive_mode"
        //                        + " when 1 then acc1.account_name"
        //                        + " when 2 then acc2.account_name"
        //                        + " when 3 then acc3.account_name"
        //                        + " end as account_name"
        //                    + ", 'Cr' as dr_or_cr"
        //                    + ", null as amount_dr"
        //                    + ", r.exclude_vat as amount_cr"
        //                    + " FROM fim_receives r"
        //                    + " cross join acm_mas_base_account cv"
        //                    + " LEFT JOIN fim_mas_receive_types rt on r.receive_type_seq = rt.receive_type_seq"
        //                    + " LEFT JOIN acm_mas_account acc1 ON cv.over_account_seq = acc1.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc2 ON rt.account_seq = acc2.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc3 ON cv.wait_income_bb_account_seq = acc3.account_seq"
        //                    + " WHERE r.receive_seq = @ref_seq"
        //                    // tax
        //                    + " union all"

        //                    + " SELECT acc1.account_seq as account_seq"
        //                    + ", acc1.account_code as account_code"
        //                    + ", acc1.account_name as account_name"
        //                    + ", 'Cr' as dr_or_cr"
        //                    + ", null as amount_dr"
        //                    + ", r.vat_amount as amount_cr"
        //                    + " FROM fim_receives r"
        //                    + " cross join acm_mas_base_account cv"
        //                    + " LEFT JOIN acm_mas_account acc1 ON cv.sale_vat_account_seq = acc1.account_seq"
        //                    + " WHERE r.receive_seq = @ref_seq";
        //                break;
        //            case "fim_money_returns":
        //                strSQL = "SELECT exp.account_seq"
        //                    + ", acc1.account_code"
        //                    + ", acc1.account_name"
        //                    + ", 'Dr' as dr_or_cr"
        //                    + ", sum(coalesce(retd.amount, 0)) as amount_dr"
        //                    + ", null as amount_cr"
        //                    + " FROM fim_money_returns ret"
        //                    + " LEFT JOIN fim_money_return_dtls retd on ret.return_seq = retd.return_seq"
        //                        + " and retd.record_status not like 'D'"
        //                    + " LEFT JOIN fim_money_request_dtls mrd on retd.money_req_dtls_seq = mrd.money_req_dtls_seq"
        //                    + " LEFT JOIN bgm_project_operation_expense opexp on mrd.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
        //                    + " LEFT JOIN bgm_project_expense pjexp on opexp.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
        //                    + " LEFT JOIN bgm_mas_expense exp on pjexp.bgm_exp_seq = exp.bgm_exp_seq"
        //                    + " LEFT JOIN acm_mas_account acc1 ON exp.account_seq = acc1.account_seq"
        //                    + " WHERE ret.return_seq = @ref_seq"
        //                    + " group by exp.account_seq, acc1.account_code, acc1.account_name"

        //                    + " union all"

        //                    + " SELECT ba.loan_account_seq as account_seq"
        //                    + ", acc1.account_code"
        //                    + ", acc1.account_name"
        //                    + ", 'Cr' as dr_or_cr"
        //                    + ", null as amount_dr"
        //                    + ", retd.amount as amount_cr"
        //                    + " FROM fim_money_returns ret"
        //                    + " LEFT JOIN (select retd.return_seq"
        //                        + ", sum(coalesce(retd.amount, 0)) as amount"
        //                        + " from fim_money_return_dtls retd"
        //                        + " where retd.record_status not like 'D'"
        //                        + " group by retd.return_seq"
        //                        + ") retd on ret.return_seq = retd.return_seq"
        //                    + " CROSS JOIN acm_mas_base_account ba"
        //                    + " LEFT JOIN acm_mas_account acc1 ON ba.loan_account_seq = acc1.account_seq"
        //                    + " WHERE ret.return_seq = @ref_seq";
        //                break;
        //            case "icm_delivery_note":
        //                strSQL = "SELECT case when ri.ResItemId is not null"
        //                        + " then rg.AssetAccountId"
        //                        + " else ex.account_seq"
        //                        + " end as account_seq"
        //                    + ", case when ri.ResItemId is not null"
        //                        + " then acc1.account_code"
        //                        + " else acc2.account_code"
        //                        + " end as account_code"
        //                    + ", case when ri.ResItemId is not null"
        //                        + " then acc1.account_name"
        //                        + " else acc2.account_name"
        //                        + " end as account_name"
        //                    + ", 'Dr' as dr_or_cr"
        //                    + ", ni.NetAmount as amount_dr"
        //                    + ", null as amount_cr"

        //                    + " FROM IcmDeliveryNoteItem ni"
        //                    + " left join IcmPurchaseOrderInstallmentItem ii on ni.PoInstallItemId = ii.PoInstallItemId"
        //                    + " left join IcmBidderQuotation q on ii.BidderQuoId = q.BidderQuoId"
        //                    + " left join IcmPurchaseRequestItem ri on q.PrItemId = ri.PrItemId"
        //                    + " left join bgm_mas_expense ex on ri.BgExpId = ex.bgm_exp_seq"
        //                    + " left join IcmMasResourceItem mi on ri.ResItemId = mi.ResItemId"
        //                    + " left join IcmMasResourceSubGroup sg on mi.ResSubGrpId = sg.ResSubGrpId"
        //                    + " left join IcmMasResourceGroup rg on sg.ResGrpId = rg.ResGrpId"
        //                    + " LEFT JOIN acm_mas_account acc1 ON rg.AssetAccountId = acc1.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc2 ON ex.account_seq = acc2.account_seq"
        //                    + " WHERE ni.DnId = @ref_seq"

        //                    + " union all"

        //                    + " SELECT bacc.direct_pay_account_seq as account_seq"
        //                    + ", acc1.account_code"
        //                    + ", acc1.account_name"
        //                    + ", 'Cr' as dr_or_cr"
        //                    + ", null as amount_dr"
        //                    + ", sum(coalesce(ni.NetAmount, 0) as amount_cr"

        //                    + " FROM IcmDeliveryNoteItem ni"
        //                    + " cross join acm_mas_base_account bacc"
        //                    + " LEFT JOIN acm_mas_account acc1 ON bacc.direct_pay_account_seq = acc1.account_seq"
        //                    + " WHERE ni.DnId = @ref_seq"
        //                    + " GROUP BY ni.DnId";
        //                break;
        //            case "fim_transfers":
        //                strSQL = "SELECT case t.transfer_type"
        //                        + " when '1' then acc2.account_seq"
        //                        + " when '2' then acc3.account_seq"
        //                        + " when '3' then acc2.account_seq"
        //                        + " else 0"
        //                        + " end as account_seq"
        //                    + ", case t.transfer_type"
        //                        + " when '1' then acc2.account_code"
        //                        + " when '2' then acc3.account_code"
        //                        + " when '3' then acc2.account_code"
        //                        + " else ''"
        //                        + " end as account_code"
        //                    + ", case t.transfer_type"
        //                        + " when '1' then acc2.account_name"
        //                        + " when '2' then acc3.account_name"
        //                        + " when '3' then acc2.account_name"
        //                        + " else ''"
        //                        + " end as account_name"
        //                    + ", 'Dr' as dr_or_cr"
        //                    + ", coalesce(t.amount, 0) as amount_dr"
        //                    + ", null as amount_cr"
        //                    + " FROM fim_transfers t"
        //                    + " CROSS JOIN acm_mas_base_account ba"
        //                    + " LEFT JOIN fim_mas_bank_accounts bacc1 on t.from_bank_acc_seq = bacc1.bank_acc_seq"
        //                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on t.to_bank_acc_seq = bacc2.bank_acc_seq"

        //                    + " LEFT JOIN acm_mas_account acc1 ON bacc1.account_seq = acc1.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc2 ON bacc2.account_seq = acc2.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc3 ON ba.cash_account_seq = acc3.account_seq"

        //                    + " WHERE t.transfer_seq = @ref_seq"

        //                    + " union all"

        //                    + " SELECT case t.transfer_type"
        //                        + " when '1' then acc3.account_seq"
        //                        + " when '2' then acc1.account_seq"
        //                        + " when '3' then acc1.account_seq"
        //                        + " else 0"
        //                        + " end as account_seq"
        //                    + ", case t.transfer_type"
        //                        + " when '1' then acc3.account_code"
        //                        + " when '2' then acc1.account_code"
        //                        + " when '3' then acc1.account_code"
        //                        + " else ''"
        //                        + " end as account_code"
        //                    + ", case t.transfer_type"
        //                        + " when '1' then acc3.account_name"
        //                        + " when '2' then acc1.account_name"
        //                        + " when '3' then acc1.account_name"
        //                        + " else ''"
        //                        + " end as account_name"
        //                    + ", 'Cr' as dr_or_cr"
        //                    + ", null as amount_dr"
        //                    + ", coalesce(t.amount, 0) as amount_cr"
        //                    + " FROM fim_transfers t"
        //                    + " CROSS JOIN acm_mas_base_account ba"
        //                    + " LEFT JOIN fim_mas_bank_accounts bacc1 on t.from_bank_acc_seq = bacc1.bank_acc_seq"
        //                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on t.to_bank_acc_seq = bacc2.bank_acc_seq"

        //                    + " LEFT JOIN acm_mas_account acc1 ON bacc1.account_seq = acc1.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc2 ON bacc2.account_seq = acc2.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc3 ON ba.cash_account_seq = acc3.account_seq"

        //                    + " WHERE t.transfer_seq = @ref_seq";
        //                break;
        //        }

        //        if (strSQL.Length > 0)
        //        {
        //            drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //            if (drc.Count > 0)
        //            {
        //                for (int i = 0; i < drc.Count; i++)
        //                {
        //                    if (cn.ConvertNullInt(drc[i]["amount_dr"]) != null || cn.ConvertNullInt(drc[i]["amount_cr"]) != null)
        //                    {
        //                        M_ACM_GENERATE_ACCOUNT_Auto info = new M_ACM_GENERATE_ACCOUNT_Auto();
        //                        info.account_seq = cn.ConvertNullInt(drc[i]["account_seq"]);
        //                        info.dr_or_cr = drc[i]["dr_or_cr"].ToString();
        //                        info.account_code = drc[i]["account_code"].ToString();
        //                        info.account_name = drc[i]["account_name"].ToString();
        //                        info.amount_dr = cn.ConvertNullDouble(drc[i]["amount_dr"]);
        //                        info.amount_cr = cn.ConvertNullDouble(drc[i]["amount_cr"]);
        //                        infoList.Add(info);
        //                    }
        //                }
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //    return infoList;
        //}

        //public IList<M_ACM_GENERATE_ACCOUNT_Auto> GetListDetail(Nullable<Int32> ref_seq, string ref_type)
        //{
        //    DataRowCollection drc = null;
        //    dbManager.CreateParameters(1);
        //    dbManager.AddParameters(0, "@ref_seq", ref_seq);
        //    IList<M_ACM_GENERATE_ACCOUNT_Auto> infoList = new List<M_ACM_GENERATE_ACCOUNT_Auto>();
        //    try
        //    {
        //        dbManager.Open();
        //        string strSQL = "";
        //        string strEventSeq = "";

        //        switch (ref_type)
        //        {
        //            #region fim_petitions
        //            case "fim_petitions":
        //                strSQL = "SELECT ba.wait_income_bb_account_seq as account_seq"
        //                    + ", acc.account_code"
        //                    + ", acc.account_name"
        //                    + ", 'Dr' as dr_or_cr"
        //                    + ", ptd.amount as amount_dr"
        //                    + ", null as amount_cr"
        //                    + " FROM fim_petitions pt"
        //                    + " LEFT JOIN (select petition_seq"
        //                        + " , sum(coalesce(amount_real, 0)) as amount"
        //                        + " from fim_petition_dtls"
        //                        + " where record_status not like 'D'"
        //                        + " group by petition_seq"
        //                        + " ) ptd ON pt.petition_seq = ptd.petition_seq"
        //                    + " cross join acm_mas_base_account ba"
        //                    + " LEFT JOIN acm_mas_account acc ON ba.wait_income_bb_account_seq = acc.account_seq"
        //                    + " WHERE pt.petition_seq = @ref_seq"

        //                    + " union all"

        //                    + " SELECT ba.income_bb_account_seq as account_seq"
        //                    + ", acc.account_code"
        //                    + ", acc.account_name"
        //                    + ", 'Cr' as dr_or_cr"
        //                    + ", null as amount_dr"
        //                    + ", ptd.amount as amount_cr"
        //                    + " FROM fim_petitions pt"
        //                    + " LEFT JOIN (select petition_seq"
        //                        + " , sum(coalesce(amount_real, 0)) as amount"
        //                        + " from fim_petition_dtls"
        //                        + " where record_status not like 'D'"
        //                        + " group by petition_seq"
        //                        + " ) ptd ON pt.petition_seq = ptd.petition_seq"
        //                    + " cross join acm_mas_base_account ba"
        //                    + " LEFT JOIN acm_mas_account acc ON ba.income_bb_account_seq = acc.account_seq"
        //                    + " WHERE pt.petition_seq = @ref_seq";
        //                break;
        //            #endregion

        //            #region fim_money_requests
        //            case "fim_money_requests":
        //                strSQL = "SELECT event_seq"
        //                    + " FROM fim_money_requests"
        //                    + " WHERE money_req_seq = @ref_seq";
        //                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

        //                if (drc.Count > 0)
        //                {
        //                    strEventSeq = drc[0]["event_seq"].ToString();
        //                }

        //                switch (strEventSeq)
        //                {
        //                    case "11":
        //                        strSQL = "SELECT ba.direct_pay_account_seq as account_seq"
        //                            + ", acc.account_code, acc.account_name"
        //                            + ", 'Dr' as dr_or_cr"
        //                            + ", mrd.amount as amount_dr"
        //                            + ", null as amount_cr"
        //                            + " FROM fim_money_requests mr"
        //                            + " LEFT JOIN (select mrd.money_req_seq"
        //                                + ", sum(coalesce(mrd.amount, 0)) as amount"
        //                                + " from fim_money_request_dtls mrd"
        //                                + " where mrd.record_status not like 'D'"
        //                                + " group by mrd.money_req_seq"
        //                                + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
        //                            + " cross join acm_mas_base_account ba"
        //                            + " LEFT JOIN acm_mas_account acc ON ba.direct_pay_account_seq = acc.account_seq"
        //                            + " WHERE mr.money_req_seq = @ref_seq"

        //                            + " UNION ALL"

        //                            + " SELECT ba.dn_cr_account_seq as account_seq"
        //                            + ", acc.account_code, acc.account_name"
        //                            + ", 'Cr' as dr_or_cr"
        //                            + ", null as amount_dr"
        //                            + ", mrd.amount as amount_cr"
        //                            + " FROM fim_money_requests mr"
        //                            + " LEFT JOIN (select mrd.money_req_seq"
        //                                + ", sum(coalesce(mrd.amount, 0)) as amount"
        //                                + " from fim_money_request_dtls mrd"
        //                                + " where mrd.record_status not like 'D'"
        //                                + " group by mrd.money_req_seq"
        //                                + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
        //                            + " cross join acm_mas_base_account ba"
        //                            + " LEFT JOIN acm_mas_account acc ON ba.dn_cr_account_seq = acc.account_seq"
        //                            + " WHERE mr.money_req_seq = @ref_seq";
        //                        break;
        //                    case "12":
        //                    case "14":
        //                    case "15":
        //                    case "17":
        //                    case "18":
        //                        strSQL = "SELECT exp.account_seq as account_seq"
        //                            + ", acc.account_code, acc.account_name"
        //                            + ", 'Dr' as dr_or_cr"
        //                            + ", mrd.amount as amount_dr"
        //                            + ", null as amount_cr"
        //                            //+ ", j.job_site_gfmis as cost_center"
        //                            + " FROM fim_money_requests mr"
        //                            + " LEFT JOIN fim_money_request_dtls mrd ON mr.money_req_seq = mrd.money_req_seq"
        //                            + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
        //                            + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
        //                            + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
        //                            //+ " LEFT JOIN hrm_mp_job_sites j ON mrd.job_site_seq = j.job_site_seq"
        //                            + " LEFT JOIN acm_mas_account acc ON exp.account_seq = acc.account_seq"
        //                            + " WHERE mr.money_req_seq = @ref_seq"
        //                            + " AND mrd.record_status not like 'D'"

        //                            + " UNION ALL"

        //                            + " SELECT ba.remain_account_seq as account_seq"
        //                            + ", acc.account_code, acc.account_name"
        //                            + ", 'Cr' as dr_or_cr"
        //                            + ", null as amount_dr"
        //                            + ", mrd.amount as amount_cr"
        //                            + " FROM fim_money_requests mr"
        //                            + " LEFT JOIN (select mrd.money_req_seq"
        //                                + ", sum(coalesce(mrd.amount, 0)) as amount"
        //                                + " from fim_money_request_dtls mrd"
        //                                + " where mrd.record_status not like 'D'"
        //                                + " group by mrd.money_req_seq"
        //                                + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
        //                            + " cross join acm_mas_base_account ba"
        //                            + " LEFT JOIN acm_mas_account acc ON ba.remain_account_seq = acc.account_seq"
        //                            + " WHERE mr.money_req_seq = @ref_seq";
        //                        break;
        //                }
        //                break;
        //            #endregion

        //            #region fim_payments
        //            case "fim_payments":
        //                strSQL = "SELECT event_seq"
        //                    + " FROM fim_payments"
        //                    + " WHERE pay_seq = @ref_seq";
        //                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

        //                if (drc.Count > 0)
        //                {
        //                    strEventSeq = drc[0]["event_seq"].ToString();
        //                }

        //                switch (strEventSeq)
        //                {
        //                    case "11":
        //                        // ค่าใช้จ่าย
        //                        strSQL = "SELECT ba.dn_cr_account_seq as account_seq"
        //                            + ", acc1.account_code as account_code"
        //                            + ", acc1.account_name account_name"
        //                            + ", 'Dr' as dr_or_cr"
        //                            //+ ", coalesce(pay.amount, 0) - coalesce(pay.vat, 0) as amount_dr"
        //                            + ", coalesce(pay.amount, 0) + coalesce(pay.tax, 0) - coalesce(pay.vat, 0) as amount_dr"
        //                            + ", null as amount_cr"
        //                            + " FROM fim_payments pay"
        //                            + " cross join acm_mas_base_account ba"
        //                            + " LEFT JOIN acm_mas_account acc1 ON ba.dn_cr_account_seq = acc1.account_seq"
        //                            + " WHERE pay.pay_seq = @ref_seq"

        //                            + " union all"
        //                            // ภาษีซื้อ
        //                            + " SELECT ba.buy_vat_account_seq as account_seq"
        //                            + ", acc1.account_code as account_code"
        //                            + ", acc1.account_name as account_name"
        //                            + ", 'Dr' as dr_or_cr"
        //                            + ", pay.vat as amount_dr"
        //                            + ", null as amount_cr"
        //                            + " FROM fim_payments pay"
        //                            + " cross join acm_mas_base_account ba"
        //                            + " LEFT JOIN acm_mas_account acc1 ON ba.buy_vat_account_seq = acc1.account_seq"
        //                            + " WHERE pay.pay_seq = @ref_seq"

        //                            + " union all"
        //                            // เครดิต เงินสด/เงินฝาก
        //                            + " SELECT case pay.method_seq"
        //                                + " when 1 then ba.cash_account_seq"
        //                                + " when 2 then bacc.account_seq"
        //                                + " when 3 then case coalesce(pet.direct_pay, '0')"
        //                                                + " when '0' then bacc.account_seq"
        //                                                + " when '1' then ba.income_bb_account_seq"
        //                                                + " end"
        //                                + " end as account_seq"
        //                            + ", case pay.method_seq"
        //                                + " when 1 then acc2.account_code"
        //                                + " when 2 then acc1.account_code"
        //                                + " when 3 then case coalesce(pet.direct_pay, '0')"
        //                                                + " when '0' then acc1.account_code"
        //                                                + " when '1' then acc3.account_code"
        //                                                + " end"
        //                                + " end as account_code"
        //                            + ", case pay.method_seq"
        //                                + " when 1 then acc2.account_name"
        //                                + " when 2 then acc1.account_name"
        //                                + " when 3 then case coalesce(pet.direct_pay, '0')"
        //                                                + " when '0' then acc1.account_name"
        //                                                + " when '1' then acc3.account_name"
        //                                                + " end"
        //                                + " end as account_name"
        //                            + ", 'Cr' as dr_or_cr"
        //                            + ", null as amount_dr"
        //                            + ", pay.amount as amount_cr"
        //                            + " FROM fim_payments pay"
        //                            + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
        //                                + " and pc.record_status not like 'D'"
        //                            + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
        //                            + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
        //                            + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
        //                            + " cross join acm_mas_base_account ba"
        //                            + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
        //                            + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
        //                            + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
        //                            + " WHERE pay.pay_seq = @ref_seq"

        //                            + " union all"
        //                            // Tax
        //                            + " SELECT case c.ConGrpId"
        //                                + " when 47 then ba.personal_tax_account_seq"
        //                                + " else ba.company_tax_account_seq"
        //                                + " end as account_seq"
        //                            + ", case c.ConGrpId"
        //                                + " when 47 then acc1.account_code"
        //                                + " else acc2.account_code"
        //                                + " end as account_code"
        //                            + ", case c.ConGrpId"
        //                                + " when 47 then acc1.account_name"
        //                                + " else acc2.account_name"
        //                                + " end as account_name"
        //                            + ", 'Cr' as dr_or_cr"
        //                            + ", null as amount_dr"
        //                            + ", pay.tax as amount_cr"
        //                            + " FROM fim_payments pay"
        //                            + " cross join acm_mas_base_account ba"
        //                            + " LEFT JOIN IcmMasContact c ON pay.contact_seq = c.ConId"
        //                            + " LEFT JOIN acm_mas_account acc1 ON ba.personal_tax_account_seq = acc1.account_seq"
        //                            + " LEFT JOIN acm_mas_account acc2 ON ba.company_tax_account_seq = acc2.account_seq"
        //                            + " WHERE pay.pay_seq = @ref_seq"
        //                            ;
        //                        break;
        //                    default:
        //                        // ค่าใช้จ่าย
        //                        strSQL = "SELECT case pay.event_seq"
        //                                + " when 16 then ba.loan_account_seq"
        //                                + " when 18 then ba.loan_account_seq"
        //                                + " else ba.remain_account_seq"
        //                                + " end as account_seq"
        //                            + ", case pay.event_seq"
        //                                + " when 16 then acc1.account_code"
        //                                + " when 18 then acc1.account_code"
        //                                + " else acc2.account_code"
        //                                + " end as account_code"
        //                            + ", case pay.event_seq"
        //                                + " when 16 then acc1.account_name"
        //                                + " when 18 then acc1.account_name"
        //                                + " else acc2.account_name"
        //                                + " end as account_name"
        //                            + ", 'Dr' as dr_or_cr"
        //                            + ", coalesce(pay.amount, 0) + coalesce(pay.tax, 0) - coalesce(pay.vat, 0) as amount_dr"
        //                            + ", null as amount_cr"
        //                            + " FROM fim_payments pay"
        //                            + " cross join acm_mas_base_account ba"
        //                            + " LEFT JOIN acm_mas_account acc1 ON ba.loan_account_seq = acc1.account_seq"
        //                            + " LEFT JOIN acm_mas_account acc2 ON ba.remain_account_seq = acc2.account_seq"
        //                            + " WHERE pay.pay_seq = @ref_seq"

        //                            + " union all"
        //                            // ภาษีซื้อ
        //                            + " SELECT ba.buy_vat_account_seq as account_seq"
        //                            + ", acc1.account_code as account_code"
        //                            + ", acc1.account_name as account_name"
        //                            + ", 'Dr' as dr_or_cr"
        //                            + ", pay.vat as amount_dr"
        //                            + ", null as amount_cr"
        //                            + " FROM fim_payments pay"
        //                            + " cross join acm_mas_base_account ba"
        //                            + " LEFT JOIN acm_mas_account acc1 ON ba.buy_vat_account_seq = acc1.account_seq"
        //                            + " WHERE pay.pay_seq = @ref_seq"

        //                            + " union all"
        //                            // เครดิต เงินสด/เงินฝาก
        //                            + " SELECT case pay.method_seq"
        //                                + " when 1 then ba.cash_account_seq"
        //                                + " when 2 then bacc.account_seq"
        //                                + " when 3 then case coalesce(pet.direct_pay, '0')"
        //                                                + " when '0' then bacc.account_seq"
        //                                                + " when '1' then ba.income_bb_account_seq"
        //                                                + " end"
        //                                + " end as account_seq"
        //                            + ", case pay.method_seq"
        //                                + " when 1 then acc2.account_code"
        //                                + " when 2 then acc1.account_code"
        //                                + " when 3 then case coalesce(pet.direct_pay, '0')"
        //                                                + " when '0' then acc1.account_code"
        //                                                + " when '1' then acc3.account_code"
        //                                                + " end"
        //                                + " end as account_code"
        //                            + ", case pay.method_seq"
        //                                + " when 1 then acc2.account_name"
        //                                + " when 2 then acc1.account_name"
        //                                + " when 3 then case coalesce(pet.direct_pay, '0')"
        //                                                + " when '0' then acc1.account_name"
        //                                                + " when '1' then acc3.account_name"
        //                                                + " end"
        //                                + " end as account_name"
        //                            + ", 'Cr' as dr_or_cr"
        //                            + ", null as amount_dr"
        //                            + ", pay.amount as amount_cr"
        //                            + " FROM fim_payments pay"
        //                            + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
        //                                + " and pc.record_status not like 'D'"
        //                            + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
        //                            + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
        //                            + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
        //                            + " cross join acm_mas_base_account ba"
        //                            + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
        //                            + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
        //                            + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
        //                            + " WHERE pay.pay_seq = @ref_seq"

        //                            + " union all"
        //                            // Tax
        //                            + " SELECT case c.ConGrpId"
        //                                + " when 47 then ba.personal_tax_account_seq"
        //                                + " else ba.company_tax_account_seq"
        //                                + " end as account_seq"
        //                            + ", case c.ConGrpId"
        //                                + " when 47 then acc1.account_code"
        //                                + " else acc2.account_code"
        //                                + " end as account_code"
        //                            + ", case c.ConGrpId"
        //                                + " when 47 then acc1.account_name"
        //                                + " else acc2.account_name"
        //                                + " end as account_name"
        //                            + ", 'Cr' as dr_or_cr"
        //                            + ", null as amount_dr"
        //                            + ", pay.tax as amount_cr"
        //                            + " FROM fim_payments pay"
        //                            + " cross join acm_mas_base_account ba"
        //                            + " LEFT JOIN IcmMasContact c ON pay.contact_seq = c.ConId"
        //                            //Modified on 19/5/57
        //                            //+ " LEFT JOIN acm_mas_account acc1 ON ba.company_tax_account_seq = acc1.account_seq"
        //                            //+ " LEFT JOIN acm_mas_account acc2 ON ba.personal_tax_account_seq = acc2.account_seq"
        //                            + " LEFT JOIN acm_mas_account acc1 ON ba.personal_tax_account_seq = acc1.account_seq"
        //                            + " LEFT JOIN acm_mas_account acc2 ON ba.company_tax_account_seq = acc2.account_seq"
        //                            + " WHERE pay.pay_seq = @ref_seq"
        //                            ;
        //                        break;
        //                }

        //                break;
        //            #endregion

        //            #region fim_receives
        //            case "fim_receives":
        //                // bank or cash
        //                strSQL = "SELECT case when r.method_seq = 1 or r.method_seq = 2"
        //                        + " then cv.cash_account_seq"
        //                        + " else ba.account_seq"
        //                        + " end as account_seq"
        //                    + ", case when r.method_seq = 1 or r.method_seq = 2"
        //                        + " then acc1.account_code"
        //                        + " else acc2.account_code"
        //                        + " end as account_code"
        //                    + ", case when r.method_seq = 1 or r.method_seq = 2"
        //                        + " then acc1.account_name"
        //                        + " else acc2.account_name"
        //                        + " end as account_name"
        //                    + ", 'Dr' as dr_or_cr"
        //                    + ", r.amount as amount_dr"
        //                    + ", null as amount_cr"
        //                    + " FROM fim_receives r"
        //                    + " LEFT JOIN fim_mas_bank_accounts ba ON r.bank_acc_seq = ba.bank_acc_seq"
        //                    + " cross join acm_mas_base_account cv"
        //                    + " LEFT JOIN acm_mas_account acc1 ON cv.cash_account_seq = acc1.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc2 ON ba.account_seq = acc2.account_seq"
        //                    + " WHERE r.receive_seq = @ref_seq"
        //                    // tax
        //                    + " union all"

        //                    + " SELECT case c.ConGrpId"
        //                        + " when 47 then cv.personal_tax_account_seq"
        //                        + " else cv.company_tax_account_seq"
        //                        + " end as account_seq"
        //                    + ", case c.ConGrpId"
        //                        + " when 47 then acc1.account_code"
        //                        + " else acc2.account_code"
        //                        + " end as account_code"
        //                    + ", case c.ConGrpId"
        //                        + " when 47 then acc1.account_name"
        //                        + " else acc2.account_name"
        //                        + " end as account_name"
        //                    + ", 'Dr' as dr_or_cr"
        //                    + ", r.tax as amount_dr"
        //                    + ", null as amount_cr"
        //                    + " FROM fim_receives r"
        //                    + " cross join acm_mas_base_account cv"
        //                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c.ConId"
        //                    + " LEFT JOIN acm_mas_account acc1 ON cv.company_tax_account_seq = acc1.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc2 ON cv.personal_tax_account_seq = acc2.account_seq"
        //                    + " WHERE r.receive_seq = @ref_seq"
        //                    // income
        //                    + " union all"

        //                    + " SELECT case r.receive_mode"
        //                        + " when 1 then case when mr.event_seq = 16 then cv.loan_account_seq"
        //                            + " else cv.over_account_seq"
        //                            + " end"
        //                        + " when 2 then rt.account_seq"
        //                        + " when 3 then cv.wait_income_bb_account_seq"
        //                        + " end as account_seq"
        //                    + ", case r.receive_mode"
        //                        + " when 1 then case when mr.event_seq = 16 then acc4.account_code"
        //                            + " else acc1.account_code"
        //                            + " end"
        //                        + " when 2 then acc2.account_code"
        //                        + " when 3 then acc3.account_code"
        //                        + " end as account_code"
        //                    + ", case r.receive_mode"
        //                        + " when 1 then case when mr.event_seq = 16 then acc4.account_name"
        //                            + " else acc1.account_name"
        //                            + " end"
        //                        + " when 2 then acc2.account_name"
        //                        + " when 3 then acc3.account_name"
        //                        + " end as account_name"
        //                    + ", 'Cr' as dr_or_cr"
        //                    + ", null as amount_dr"
        //                    + ", r.exclude_vat as amount_cr"
        //                    + " FROM fim_receives r"
        //                    + " LEFT JOIN fim_money_requests mr on r.money_req_seq = mr.money_req_seq"
        //                    + " cross join acm_mas_base_account cv"
        //                    + " LEFT JOIN fim_mas_receive_types rt on r.receive_type_seq = rt.receive_type_seq"
        //                    + " LEFT JOIN acm_mas_account acc1 ON cv.over_account_seq = acc1.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc2 ON rt.account_seq = acc2.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc3 ON cv.wait_income_bb_account_seq = acc3.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc4 ON cv.loan_account_seq = acc4.account_seq"
        //                    + " WHERE r.receive_seq = @ref_seq"
        //                    // tax
        //                    + " union all"

        //                    + " SELECT acc1.account_seq as account_seq"
        //                    + ", acc1.account_code as account_code"
        //                    + ", acc1.account_name as account_name"
        //                    + ", 'Cr' as dr_or_cr"
        //                    + ", null as amount_dr"
        //                    + ", r.vat_amount as amount_cr"
        //                    + " FROM fim_receives r"
        //                    + " cross join acm_mas_base_account cv"
        //                    + " LEFT JOIN acm_mas_account acc1 ON cv.sale_vat_account_seq = acc1.account_seq"
        //                    + " WHERE r.receive_seq = @ref_seq";
        //                break;
        //            #endregion

        //            #region fim_money_returns
        //            case "fim_money_returns":
        //                strSQL = "SELECT exp.account_seq"
        //                    + ", acc1.account_code"
        //                    + ", acc1.account_name"
        //                    + ", 'Dr' as dr_or_cr"
        //                    + ", sum(coalesce(retd.amount, 0)) as amount_dr"
        //                    + ", null as amount_cr"
        //                    + " FROM fim_money_returns ret"
        //                    + " LEFT JOIN fim_money_return_dtls retd on ret.return_seq = retd.return_seq"
        //                        + " and retd.record_status not like 'D'"
        //                    + " LEFT JOIN fim_money_request_dtls mrd on retd.money_req_dtls_seq = mrd.money_req_dtls_seq"
        //                    + " LEFT JOIN bgm_project_operation_expense opexp on mrd.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
        //                    + " LEFT JOIN bgm_project_expense pjexp on opexp.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
        //                    + " LEFT JOIN bgm_mas_expense exp on pjexp.bgm_exp_seq = exp.bgm_exp_seq"
        //                    + " LEFT JOIN acm_mas_account acc1 ON exp.account_seq = acc1.account_seq"
        //                    + " WHERE ret.return_seq = @ref_seq"
        //                    + " group by exp.account_seq, acc1.account_code, acc1.account_name"

        //                    + " union all"

        //                    + " SELECT ba.loan_account_seq as account_seq"
        //                    + ", acc1.account_code"
        //                    + ", acc1.account_name"
        //                    + ", 'Cr' as dr_or_cr"
        //                    + ", null as amount_dr"
        //                    + ", retd.amount as amount_cr"
        //                    + " FROM fim_money_returns ret"
        //                    + " LEFT JOIN (select retd.return_seq"
        //                        + ", sum(coalesce(retd.amount, 0)) as amount"
        //                        + " from fim_money_return_dtls retd"
        //                        + " where retd.record_status not like 'D'"
        //                        + " group by retd.return_seq"
        //                        + ") retd on ret.return_seq = retd.return_seq"
        //                    + " CROSS JOIN acm_mas_base_account ba"
        //                    + " LEFT JOIN acm_mas_account acc1 ON ba.loan_account_seq = acc1.account_seq"
        //                    + " WHERE ret.return_seq = @ref_seq";
        //                break;
        //            #endregion

        //            #region icm_delivery_note
        //            case "icm_delivery_note":
        //                strSQL = "SELECT case when ri.ResItemId is not null"
        //                        + " then rg.AssetAccountId"
        //                        + " else ex.account_seq"
        //                        + " end as account_seq"
        //                    + ", case when ri.ResItemId is not null"
        //                        + " then acc1.account_code"
        //                        + " else acc2.account_code"
        //                        + " end as account_code"
        //                    + ", case when ri.ResItemId is not null"
        //                        + " then acc1.account_name"
        //                        + " else acc2.account_name"
        //                        + " end as account_name"
        //                    + ", 'Dr' as dr_or_cr"
        //                    + ", coalesce(ni.NetAmount, 0) - coalesce(ni.VatAmount, 0) as amount_dr"
        //                    + ", null as amount_cr"

        //                    + " FROM IcmDeliveryNoteItem ni"
        //                    + " left join IcmPurchaseOrderInstallmentItem ii on ni.PoInstallItemId = ii.PoInstallItemId"
        //                    + " left join IcmBidderQuotation q on ii.BidderQuoId = q.BidderQuoId"
        //                    + " left join IcmPurchaseRequestItem ri on q.PrItemId = ri.PrItemId"
        //                    + " left join bgm_mas_expense ex on ri.BgExpId = ex.bgm_exp_seq"
        //                    + " left join IcmMasResourceItem mi on ri.ResItemId = mi.ResItemId"
        //                    + " left join IcmMasResourceSubGroup sg on mi.ResSubGrpId = sg.ResSubGrpId"
        //                    + " left join IcmMasResourceGroup rg on sg.ResGrpId = rg.ResGrpId"
        //                    + " LEFT JOIN acm_mas_account acc1 ON rg.AssetAccountId = acc1.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc2 ON ex.account_seq = acc2.account_seq"
        //                    + " WHERE ni.DnId = @ref_seq"

        //                    + " union all"

        //                    + " SELECT bacc.suspense_input_tax_account_seq as account_seq"
        //                    + ", acc1.account_code as account_code"
        //                    + ", acc1.account_name as account_name"
        //                    + ", 'Dr' as dr_or_cr"
        //                    + ", coalesce(ni.VatAmount, 0) as amount_dr"
        //                    + ", null as amount_cr"

        //                    + " FROM IcmDeliveryNoteItem ni"
        //                    + " cross join acm_mas_base_account bacc"
        //                    + " LEFT JOIN acm_mas_account acc1 ON bacc.suspense_input_tax_account_seq = acc1.account_seq"
        //                    + " WHERE ni.DnId = @ref_seq"

        //                    + " union all"

        //                    + " SELECT bacc.direct_pay_account_seq as account_seq"
        //                    + ", acc1.account_code"
        //                    + ", acc1.account_name"
        //                    + ", 'Cr' as dr_or_cr"
        //                    + ", null as amount_dr"
        //                    + ", sum(coalesce(ni.NetAmount, 0)) as amount_cr"

        //                    + " FROM IcmDeliveryNoteItem ni"
        //                    + " cross join acm_mas_base_account bacc"
        //                    + " LEFT JOIN acm_mas_account acc1 ON bacc.direct_pay_account_seq = acc1.account_seq"
        //                    + " WHERE ni.DnId = @ref_seq"
        //                    + " GROUP BY bacc.direct_pay_account_seq,acc1.account_code,acc1.account_name";
        //                break;
        //            #endregion

        //            #region fim_transfers
        //            case "fim_transfers":
        //                strSQL = "SELECT case t.transfer_type"
        //                        + " when '1' then acc2.account_seq"
        //                        + " when '2' then acc3.account_seq"
        //                        + " when '3' then acc2.account_seq"
        //                        + " else 0"
        //                        + " end as account_seq"
        //                    + ", case t.transfer_type"
        //                        + " when '1' then acc2.account_code"
        //                        + " when '2' then acc3.account_code"
        //                        + " when '3' then acc2.account_code"
        //                        + " else ''"
        //                        + " end as account_code"
        //                    + ", case t.transfer_type"
        //                        + " when '1' then acc2.account_name"
        //                        + " when '2' then acc3.account_name"
        //                        + " when '3' then acc2.account_name"
        //                        + " else ''"
        //                        + " end as account_name"
        //                    + ", 'Dr' as dr_or_cr"
        //                    + ", coalesce(t.amount, 0) as amount_dr"
        //                    + ", null as amount_cr"
        //                    + " FROM fim_transfers t"
        //                    + " CROSS JOIN acm_mas_base_account ba"
        //                    + " LEFT JOIN fim_mas_bank_accounts bacc1 on t.from_bank_acc_seq = bacc1.bank_acc_seq"
        //                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on t.to_bank_acc_seq = bacc2.bank_acc_seq"

        //                    + " LEFT JOIN acm_mas_account acc1 ON bacc1.account_seq = acc1.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc2 ON bacc2.account_seq = acc2.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc3 ON ba.cash_account_seq = acc3.account_seq"

        //                    + " WHERE t.transfer_seq = @ref_seq"

        //                    + " union all"

        //                    + " SELECT case t.transfer_type"
        //                        + " when '1' then acc3.account_seq"
        //                        + " when '2' then acc1.account_seq"
        //                        + " when '3' then acc1.account_seq"
        //                        + " else 0"
        //                        + " end as account_seq"
        //                    + ", case t.transfer_type"
        //                        + " when '1' then acc3.account_code"
        //                        + " when '2' then acc1.account_code"
        //                        + " when '3' then acc1.account_code"
        //                        + " else ''"
        //                        + " end as account_code"
        //                    + ", case t.transfer_type"
        //                        + " when '1' then acc3.account_name"
        //                        + " when '2' then acc1.account_name"
        //                        + " when '3' then acc1.account_name"
        //                        + " else ''"
        //                        + " end as account_name"
        //                    + ", 'Cr' as dr_or_cr"
        //                    + ", null as amount_dr"
        //                    + ", coalesce(t.amount, 0) as amount_cr"
        //                    + " FROM fim_transfers t"
        //                    + " CROSS JOIN acm_mas_base_account ba"
        //                    + " LEFT JOIN fim_mas_bank_accounts bacc1 on t.from_bank_acc_seq = bacc1.bank_acc_seq"
        //                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on t.to_bank_acc_seq = bacc2.bank_acc_seq"

        //                    + " LEFT JOIN acm_mas_account acc1 ON bacc1.account_seq = acc1.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc2 ON bacc2.account_seq = acc2.account_seq"
        //                    + " LEFT JOIN acm_mas_account acc3 ON ba.cash_account_seq = acc3.account_seq"

        //                    + " WHERE t.transfer_seq = @ref_seq";
        //                break;
        //            #endregion
        //        }

        //        if (strSQL.Length > 0)
        //        {
        //            drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //            if (drc.Count > 0)
        //            {
        //                for (int i = 0; i < drc.Count; i++)
        //                {
        //                    if ((cn.ConvertNullInt(drc[i]["amount_dr"]) != null && cn.ConvertNullInt(drc[i]["amount_dr"]) != 0 && drc[i]["dr_or_cr"].ToString() == "Dr")
        //                        || (cn.ConvertNullInt(drc[i]["amount_cr"]) != null && cn.ConvertNullInt(drc[i]["amount_cr"]) != 0 && drc[i]["dr_or_cr"].ToString() == "Cr"))
        //                    {
        //                        M_ACM_GENERATE_ACCOUNT_Auto info = new M_ACM_GENERATE_ACCOUNT_Auto();
        //                        info.account_seq = cn.ConvertNullInt(drc[i]["account_seq"]);
        //                        info.dr_or_cr = drc[i]["dr_or_cr"].ToString();
        //                        info.account_code = drc[i]["account_code"].ToString();
        //                        info.account_name = drc[i]["account_name"].ToString();
        //                        info.amount_dr = cn.ConvertNullDouble(drc[i]["amount_dr"]);
        //                        info.amount_cr = cn.ConvertNullDouble(drc[i]["amount_cr"]);
        //                        infoList.Add(info);
        //                    }
        //                }
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //    return infoList;
        //}

        public IList<M_ACM_GENERATE_ACCOUNT_Auto> GetListDetailDebit(Nullable<Int32> ref_seq, string ref_type)
        {
            string strEventSeq = "";
            DataRowCollection drc = null;
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@ref_seq", ref_seq);
            IList<M_ACM_GENERATE_ACCOUNT_Auto> infoList = new List<M_ACM_GENERATE_ACCOUNT_Auto>();
            try
            {
                dbManager.Open();
                string strSQL = "";

                switch (ref_type)
                {
                    case "fim_petitions":
                        strSQL = "SELECT ba.wait_income_bb_account_seq as account_seq"
                            + ", acc.account_code"
                            + ", acc.account_name"
                            + ", 'D' as dr_or_cr"
                            + ", ptd.amount as amount"
                            + ", null as cost_center"
                            + " FROM fim_petitions pt"
                            + " LEFT JOIN (select petition_seq"
                                + " , sum(coalesce(amount_real, 0)) as amount"
                                + " from fim_petition_dtls"
                                + " where record_status not like 'D'"
                                + " group by petition_seq"
                                + " ) ptd ON pt.petition_seq = ptd.petition_seq"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc ON ba.wait_income_bb_account_seq = acc.account_seq"
                            + " WHERE pt.petition_seq = @ref_seq";
                        break;
                    case "fim_money_requests":
                        strSQL = "SELECT event_seq"
                            + " FROM fim_money_requests"
                            + " WHERE money_req_seq = @ref_seq";
                        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                        if (drc.Count > 0)
                        {
                            strEventSeq = drc[0]["event_seq"].ToString();
                        }

                        switch (strEventSeq)
                        {
                            case "11":
                                strSQL = "SELECT ba.gr_ir_account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'D' as dr_or_cr"
                                    + ", mrd.amount as amount"
                                    + ", null as cost_center"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.gr_ir_account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq";
                                break;
                            case "12":
                            case "14":
                            case "15":
                            case "17":
                            case "18":
                                strSQL = "SELECT exp.account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'D' as dr_or_cr"
                                    + ", mrd.amount as amount"
                                    + ", null as cost_center"
                                    //+ ", j.job_site_gfmis as cost_center"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN fim_money_request_dtls mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
                                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                                    //+ " LEFT JOIN hrm_mp_job_sites j ON mrd.job_site_seq = j.job_site_seq"
                                    + " LEFT JOIN acm_mas_account acc ON exp.account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq"
                                    + " AND mrd.record_status not like 'D'";
                                break;
                        }
                        break;
                    case "fim_payments":
                        strSQL = "SELECT case pay.event_seq"
                                + " when 16 then ba.loan_account_seq"
                                + " when 18 then ba.loan_account_seq"
                                + " when 11 then ba.dn_cr_account_seq"
                                + " else ba.remain_account_seq"
                                + " end as account_seq"
                            + ", case pay.event_seq"
                                + " when 16 then acc1.account_code"
                                + " when 18 then acc1.account_code"
                                + " when 11 then acc2.account_code"
                                + " else acc3.account_code"
                                + " end as account_code"
                            + ", case pay.event_seq"
                                + " when 16 then acc1.account_name"
                                + " when 18 then acc1.account_name"
                                + " when 11 then acc2.account_name"
                                + " else acc3.account_name"
                                + " end as account_name"
                            + ", 'D' as dr_or_cr"
                            + ", coalesce(pay.amount, 0) - coalesce(pay.vat, 0) as amount"
                            + ", null as cost_center"
                            + " FROM fim_payments pay"
                            //+ " LEFT JOIN (select mrd.money_req_seq"
                            //    + ", sum(coalesce(mrd.amount, 0)) as amount"
                            //    + " from fim_money_request_dtls mrd"
                            //    + " where record_status not like 'D'"
                            //    + " group by mrd.money_req_seq"
                            //    + ") mrd on pay.money_req_seq = mrd.money_req_seq"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON ba.loan_account_seq = acc1.account_seq"
                            + " LEFT JOIN acm_mas_account acc2 ON ba.remain_account_seq = acc2.account_seq"
                            + " LEFT JOIN acm_mas_account acc3 ON ba.dn_cr_account_seq = acc3.account_seq"
                            + " WHERE pay.pay_seq = @ref_seq"

                            + " union all"
                            // ภาษีซื้อ
                            + " SELECT ba.buy_vat_account_seq as account_seq"
                            + ", acc1.account_code as account_code"
                            + ", acc1.account_name as account_name"
                            + ", 'D' as dr_or_cr"
                            + ", pay.vat as amount"
                            + ", null as cost_center"
                            + " FROM fim_payments pay"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON ba.buy_vat_account_seq = acc1.account_seq"
                            + " WHERE pay.pay_seq = @ref_seq";
                        break;
                    case "fim_receives":
                        strSQL = "SELECT case when r.method_seq = 1 or r.method_seq = 2"
                            + " then cv.cash_account_seq"
                            + " else ba.account_seq"
                            + " end as account_seq"
                        + ", case when r.method_seq = 1 or r.method_seq = 2"
                            + " then acc1.account_code"
                            + " else acc2.account_code"
                            + " end as account_code"
                        + ", case when r.method_seq = 1 or r.method_seq = 2"
                            + " then acc1.account_name"
                            + " else acc2.account_name"
                            + " end as account_name"
                        + ", 'D' as dr_or_cr"
                        + ", r.amount as amount"
                        + ", null as cost_center"

                        + " FROM fim_receives r"
                        + " LEFT JOIN fim_mas_bank_accounts ba ON r.bank_acc_seq = ba.bank_acc_seq"
                        + " cross join acm_mas_base_account cv"
                        + " LEFT JOIN acm_mas_account acc1 ON cv.cash_account_seq = acc1.account_seq"
                        + " LEFT JOIN acm_mas_account acc2 ON ba.account_seq = acc2.account_seq"
                        + " WHERE r.receive_seq = @ref_seq";
                        //string strMode = "";

                        //strSQL = "SELECT receive_mode"
                        //    + " FROM fim_receives"
                        //    + " WHERE receive_seq = @ref_seq";
                        //drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                        //if (drc.Count > 0)
                        //{
                        //    strMode = drc[0]["receive_mode"].ToString();
                        //}

                        //switch (strMode)
                        //{
                        //    case "1" :
                        //    case "2" :
                        //        strSQL = "SELECT case when r.method_seq = 1 or r.method_seq = 2"
                        //                + " then cv.account_cash"
                        //                + " else ba.account_seq"
                        //                + " end as account_seq"
                        //            + ", case when r.method_seq = 1 or r.method_seq = 2"
                        //                + " then acc1.account_code"
                        //                + " else acc2.account_code"
                        //                + " end as account_code"
                        //            + ", case when r.method_seq = 1 or r.method_seq = 2"
                        //                + " then acc1.account_name"
                        //                + " else acc2.account_name"
                        //                + " end as account_name"
                        //            + ", 'D' as dr_or_cr, r.amount as amount, null as cost_center"

                        //            + " FROM fim_receives r"
                        //            + " LEFT JOIN fim_mas_bank_accounts ba ON r.bank_acc_seq = ba.bank_acc_seq"
                        //            + " cross join acm_change_value cv on cv.record_status not like 'D'"
                        //            + " LEFT JOIN acm_mas_account acc1 ON cv.account_cash = acc1.account_seq"
                        //            + " LEFT JOIN acm_mas_account acc2 ON ba.account_seq = acc2.account_seq"
                        //            + " WHERE r.receive_seq = @ref_seq ";
                        //        break;
                        //    case "3" :
                        //        strSQL = "SELECT ba.account_seq"
                        //            + ", acc.account_code, acc.account_name"
                        //            + ", 'D' as dr_or_cr, r.amount as amount, null as cost_center"

                        //            + " FROM fim_receives r"
                        //            + " LEFT JOIN fim_mas_bank_accounts ba ON r.bank_acc_seq = ba.bank_acc_seq"
                        //            + " LEFT JOIN acm_mas_account acc ON ba.account_seq = acc.account_seq"
                        //            + " WHERE r.receive_seq = @ref_seq ";
                        //        break;
                        //}
                        break;
                    case "fim_money_returns":
                        strSQL = "SELECT exp.account_seq"
                            + ", acc1.account_code"
                            + ", acc1.account_name"
                            + ", 'D' as dr_or_cr"
                            + ", sum(coalesce(retd.amount, 0)) as amount"
                            + ", null as cost_center"
                            + " FROM fim_money_returns ret"
                            + " LEFT JOIN fim_money_return_dtls retd on ret.return_seq = retd.return_seq"
                                + " and retd.record_status not like 'D'"
                            + " LEFT JOIN fim_money_request_dtls mrd on retd.money_req_dtls_seq = mrd.money_req_dtls_seq"
                            + " LEFT JOIN bgm_project_operation_expense opexp on mrd.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                            + " LEFT JOIN bgm_project_expense pjexp on opexp.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
                            + " LEFT JOIN bgm_mas_expense exp on pjexp.bgm_exp_seq = exp.bgm_exp_seq"
                            + " LEFT JOIN acm_mas_account acc1 ON exp.account_seq = acc1.account_seq"
                            + " WHERE ret.return_seq = @ref_seq"
                            + " group by exp.account_seq, acc1.account_code, acc1.account_name";
                        break;
                    case "icm_delivery_note":
                        strSQL = "SELECT exp.account_seq"
                            + ", acc1.account_code"
                            + ", acc1.account_name"
                            + ", 'D' as dr_or_cr"
                            + ", sum(coalesce(retd.amount, 0)) as amount"
                            + ", null as cost_center"
                            + " FROM fim_money_returns ret"
                            + " LEFT JOIN fim_money_return_dtls retd on ret.return_seq = retd.return_seq"
                                + " and retd.record_status not like 'D'"
                            + " LEFT JOIN fim_money_request_dtls mrd on retd.money_req_dtls_seq = mrd.money_req_dtls_seq"
                            + " LEFT JOIN bgm_project_operation_expense opexp on mrd.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                            + " LEFT JOIN bgm_project_expense pjexp on opexp.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
                            + " LEFT JOIN bgm_mas_expense exp on pjexp.bgm_exp_seq = exp.bgm_exp_seq"
                            + " LEFT JOIN acm_mas_account acc1 ON exp.account_seq = acc1.account_seq"
                            + " WHERE ret.return_seq = @ref_seq"
                            + " group by exp.account_seq, acc1.account_code, acc1.account_name";
                        break;
                    case "fim_transfers":
                        strSQL = "SELECT case t.transfer_type"
                            + " when '1' then acc2.account_seq"
                            + " when '2' then acc3.account_seq"
                            + " when '3' then acc2.account_seq"
                            + " else 0"
                            + " end as account_seq"
                        + ", case t.transfer_type"
                            + " when '1' then acc2.account_code"
                            + " when '2' then acc3.account_code"
                            + " when '3' then acc2.account_code"
                            + " else ''"
                            + " end as account_code"
                        + ", case t.transfer_type"
                            + " when '1' then acc2.account_name"
                            + " when '2' then acc3.account_name"
                            + " when '3' then acc2.account_name"
                            + " else ''"
                            + " end as account_name"
                        + ", 'D' as dr_or_cr"
                        + ", coalesce(t.amount, 0) as amount"
                        + ", null as cost_center"
                        + " FROM fim_transfers t"
                        + " CROSS JOIN acm_mas_base_account ba"
                        + " LEFT JOIN fim_mas_bank_accounts bacc1 on t.from_bank_acc_seq = bacc1.bank_acc_seq"
                        + " LEFT JOIN fim_mas_bank_accounts bacc2 on t.to_bank_acc_seq = bacc2.bank_acc_seq"

                        + " LEFT JOIN acm_mas_account acc1 ON bacc1.account_seq = acc1.account_seq"
                        + " LEFT JOIN acm_mas_account acc2 ON bacc2.account_seq = acc2.account_seq"
                        + " LEFT JOIN acm_mas_account acc3 ON ba.cash_account_seq = acc3.account_seq"

                        + " WHERE t.transfer_seq = @ref_seq";
                        break;
                    default:
                        break;
                }

                if (strSQL.Length > 0)
                {
                    drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                    if (drc.Count > 0)
                    {
                        for (int i = 0; i < drc.Count; i++)
                        {
                            M_ACM_GENERATE_ACCOUNT_Auto info = new M_ACM_GENERATE_ACCOUNT_Auto();
                            info.account_seq = cn.ConvertNullInt(drc[i]["account_seq"]);
                            info.dr_or_cr = drc[i]["dr_or_cr"].ToString();
                            info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                            info.account_name = drc[i]["account_name"].ToString();
                            infoList.Add(info);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public IList<M_ACM_GENERATE_ACCOUNT_Auto> GetListDetailCredit(Nullable<Int32> ref_seq, string ref_type)
        {
            DataRowCollection drc = null;
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@ref_seq", ref_seq);
            IList<M_ACM_GENERATE_ACCOUNT_Auto> infoList = new List<M_ACM_GENERATE_ACCOUNT_Auto>();
            try
            {
                dbManager.Open();
                string strEventSeq = "";
                string strSQL = "";
                switch (ref_type)
                {
                    case "fim_petitions":
                        strSQL = "SELECT ba.income_bb_account_seq as account_seq"
                            + ", acc.account_code"
                            + ", acc.account_name"
                            + ", 'C' as dr_or_cr"
                            + ", ptd.amount as amount"
                            + ", null as cost_center"
                            + " FROM fim_petitions pt"
                            + " LEFT JOIN (select petition_seq"
                                + " , sum(coalesce(amount_real, 0)) as amount"
                                + " from fim_petition_dtls"
                                + " where record_status not like 'D'"
                                + " group by petition_seq"
                                + " ) ptd ON pt.petition_seq = ptd.petition_seq"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc ON ba.income_bb_account_seq = acc.account_seq"
                            + " WHERE pt.petition_seq = @ref_seq";
                        break;
                    case "fim_money_requests":
                        strSQL = "SELECT event_seq"
                            + " FROM fim_money_requests"
                            + " WHERE money_req_seq = @ref_seq";
                        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                        if (drc.Count > 0)
                        {
                            strEventSeq = drc[0]["event_seq"].ToString();
                        }

                        switch (strEventSeq)
                        {
                            case "11":
                                strSQL = "SELECT ba.dn_cr_account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'C' as dr_or_cr"
                                    + ", mrd.amount as amount"
                                    + ", null as cost_center"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.dn_cr_account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq";
                                break;
                            case "12":
                            case "14":
                            case "15":
                            case "17":
                            case "18":
                                strSQL = "SELECT ba.remain_account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'C' as dr_or_cr"
                                    + ", mrd.amount as amount"
                                    + ", null as cost_center"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.remain_account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq";
                                break;
                        }
                        break;
                    case "fim_payments":
                        strSQL = "SELECT case pay.method_seq"
                                + " when 1 then ba.cash_account_seq"
                                + " when 2 then bacc.account_seq"
                                + " when 3 then case coalesce(pet.direct_pay, '0')"
                                    + " when '0' then bacc.account_seq"
                                    + " when '1' then ba.income_bb_account_seq"
                                    + " end"
                                + " end as account_seq"
                            + ", case pay.method_seq"
                                + " when 1 then acc2.account_code"
                                + " when 2 then acc1.account_code"
                                + " when 3 then case coalesce(pet.direct_pay, '0')"
                                    + " when '0' then acc1.account_code"
                                    + " when '1' then acc3.account_code"
                                    + " end"
                                + " end as account_code"
                            + ", case pay.method_seq"
                                + " when 1 then acc2.account_name"
                                + " when 2 then acc1.account_name"
                                + " when 3 then case coalesce(pet.direct_pay, '0')"
                                    + " when '0' then acc1.account_name"
                                    + " when '1' then acc3.account_name"
                                    + " end"
                                + " end as account_name"
                            + ", 'C' as dr_or_cr"
                            + ", pay.amount as amount"
                            + ", null as cost_center"
                            + " FROM fim_payments pay"
                            + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                + " and pc.record_status not like 'D'"
                            + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                            + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                            + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                            + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                            + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                            + " WHERE pay.pay_seq = @ref_seq"

                            + " union all"
                            // Tax
                            + " SELECT case c.ConGrpId"
                                + " when 47 then ba.personal_tax_account_seq"
                                + " else ba.company_tax_account_seq"
                                + " end as account_seq"
                            + ", case c.ConGrpId"
                                + " when 47 then acc1.account_code"
                                + " else acc2.account_code"
                                + " end as account_code"
                            + ", case c.ConGrpId"
                                + " when 47 then acc1.account_name"
                                + " else acc2.account_name"
                                + " end as account_name"
                            + ", 'C' as dr_or_cr"
                            + ", pay.tax as amount"
                            + ", null as cost_center"
                            + " FROM fim_payments pay"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN IcmMasContact c ON pay.contact_seq = c.ConId"
                            + " LEFT JOIN acm_mas_account acc1 ON ba.company_tax_account_seq = acc1.account_seq"
                            + " LEFT JOIN acm_mas_account acc2 ON ba.personal_tax_account_seq = acc2.account_seq"
                            + " WHERE pay.pay_seq = @ref_seq";
                        break;
                    case "fim_receives":
                        strSQL = "SELECT case pay.method_seq"
                                + " when 1 then ba.cash_account_seq"
                                + " when 2 then bacc.account_seq"
                                + " when 3 then case coalesce(pet.direct_pay, '0')"
                                    + " when '0' then null"
                                    + " when '1' then ba.income_bb_account_seq"
                                    + " end"
                                + " end as account_seq"
                            + ", case pay.method_seq"
                                + " when 1 then acc2.account_code"
                                + " when 2 then acc1.account_code"
                                + " when 3 then case coalesce(pet.direct_pay, '0')"
                                    + " when '0' then null"
                                    + " when '1' then acc3.account_code"
                                    + " end"
                                + " end as account_code"
                            + ", case pay.method_seq"
                                + " when 1 then acc2.account_name"
                                + " when 2 then acc1.account_name"
                                + " when 3 then case coalesce(pet.direct_pay, '0')"
                                    + " when '0' then null"
                                    + " when '1' then acc3.account_name"
                                    + " end"
                                + " end as account_name"
                            + ", 'C' as dr_or_cr"
                            + ", pay.amount as amount"
                            + ", null as cost_center"
                            + " FROM fim_payments pay"
                            + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                + " and pc.record_status not like 'D'"
                            + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                            + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                            + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                            + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                            + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                            + " WHERE pay.pay_seq = @ref_seq"

                            + " union all"

                            + " SELECT ba.company_tax_account_seq as account_seq"
                            + ", acc1.account_code"
                            + ", acc1.account_name"
                            + ", 'C' as dr_or_cr"
                            + ", pay.tax as amount"
                            + ", null as cost_center"
                            + " FROM fim_payments pay"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON ba.company_tax_account_seq = acc1.account_seq"
                            + " WHERE pay.pay_seq = @ref_seq";
                        //string strMode = "";
                        //strSQL = "SELECT receive_mode"
                        //    + " FROM fim_receives"
                        //    + " WHERE receive_seq = @ref_seq";
                        //drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                        //if (drc.Count > 0)
                        //{
                        //    strMode = drc[0]["receive_mode"].ToString();
                        //}

                        //switch (strMode)
                        //{
                        //    case "1":
                        //        break;
                        //    case "2":
                        //        strSQL = "SELECT rt.account_seq"
                        //            + ", acc.account_code, acc.account_name"
                        //            + ", 'C' as dr_or_cr, r.amount as amount, null as cost_center"

                        //            + " FROM fim_receives r"
                        //            + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                        //            + " LEFT JOIN acm_mas_account acc ON rt.account_seq = acc.account_seq"
                        //            + " WHERE r.receive_seq = @ref_seq ";
                        //        break;
                        //    case "3":
                        //        strSQL = "SELECT cv.account_wait_receive as account_seq"
                        //            + ", acc.account_code, acc.account_name"
                        //            + ", 'C' as dr_or_cr, r.amount as amount, null as cost_center"
                        //            + " FROM fim_receives r"
                        //            + " cross join acm_change_value cv on cv.record_status not like 'D'"
                        //            + " LEFT JOIN acm_mas_account acc ON cv.account_wait_receive = acc.account_seq"
                        //            + " WHERE r.receive_seq = @ref_seq ";
                        //        break;
                        //}
                        break;
                    case "fim_money_returns":
                        strSQL = "SELECT ba.loan_account_seq as account_seq"
                            + ", acc1.account_code"
                            + ", acc1.account_name"
                            + ", 'C' as dr_or_cr"
                            + ", retd.amount as amount"
                            + ", null as cost_center"
                            + " FROM fim_money_returns ret"
                            + " LEFT JOIN (select retd.return_seq"
                                + ", sum(coalesce(retd.amount, 0)) as amount"
                                + " from fim_money_return_dtls retd"
                                + " where retd.record_status not like 'D'"
                                + " group by retd.return_seq"
                                + ") retd on ret.return_seq = retd.return_seq"
                            + " CROSS JOIN acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON ba.loan_account_seq = acc1.account_seq"
                            + " WHERE ret.return_seq = @ref_seq";
                        break;
                    case "icm_delivery_note":
                        strSQL = "SELECT bacc.gr_ir_account_seq as account_seq"
                            + ", acc1.account_code"
                            + ", acc1.account_name"
                            + ", 'C' as dr_or_cr"
                            + ", sum(coalesce(ni.NetAmount, 0) as amount_cr"
                            + ", null as cost_center"

                            + " FROM IcmDeliveryNoteItem ni"
                            + " cross join acm_mas_base_account bacc"
                            + " LEFT JOIN acm_mas_account acc1 ON bacc.gr_ir_account_seq = acc1.account_seq"
                            + " WHERE ni.DnId = @ref_seq"
                            + " GROUP BY ni.DnId";
                        break;
                    case "fim_transfers":
                        strSQL = "SELECT case t.transfer_type"
                                + " when '1' then acc3.account_seq"
                                + " when '2' then acc1.account_seq"
                                + " when '3' then acc1.account_seq"
                                + " else 0"
                                + " end as account_seq"
                            + ", case t.transfer_type"
                                + " when '1' then acc3.account_code"
                                + " when '2' then acc1.account_code"
                                + " when '3' then acc1.account_code"
                                + " else ''"
                                + " end as account_code"
                            + ", case t.transfer_type"
                                + " when '1' then acc3.account_name"
                                + " when '2' then acc1.account_name"
                                + " when '3' then acc1.account_name"
                                + " else ''"
                                + " end as account_name"
                            + ", 'C' as dr_or_cr"
                            + ", coalesce(t.amount, 0) as amount"
                            + ", null as cost_center"
                            + " FROM fim_transfers t"
                            + " CROSS JOIN acm_mas_base_account ba"
                            + " LEFT JOIN fim_mas_bank_accounts bacc1 on t.from_bank_acc_seq = bacc1.bank_acc_seq"
                            + " LEFT JOIN fim_mas_bank_accounts bacc2 on t.to_bank_acc_seq = bacc2.bank_acc_seq"

                            + " LEFT JOIN acm_mas_account acc1 ON bacc1.account_seq = acc1.account_seq"
                            + " LEFT JOIN acm_mas_account acc2 ON bacc2.account_seq = acc2.account_seq"
                            + " LEFT JOIN acm_mas_account acc3 ON ba.cash_account_seq = acc3.account_seq"

                            + " WHERE t.transfer_seq = @ref_seq";
                        break;
                    default:
                        break;
                }

                if (strSQL.Length > 0)
                {
                    drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                    if (drc.Count > 0)
                    {
                        for (int i = 0; i < drc.Count; i++)
                        {
                            M_ACM_GENERATE_ACCOUNT_Auto info = new M_ACM_GENERATE_ACCOUNT_Auto();
                            info.account_seq = cn.ConvertNullInt(drc[i]["account_seq"]);
                            info.dr_or_cr = drc[i]["dr_or_cr"].ToString();
                            info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                            info.account_name = drc[i]["account_name"].ToString();
                            infoList.Add(info);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        //public IList<M_ACM_GENERATE_ACCOUNT_Auto> GetListFilterDetail(Nullable<Int32> ref_seq,string ref_type)
        //{
        //    DataRowCollection drc = null;
        //    dbManager.CreateParameters(1);
        //    dbManager.AddParameters(0, "@ref_seq", ref_seq);
        //    IList<M_ACM_GENERATE_ACCOUNT_Auto> infoList = new List<M_ACM_GENERATE_ACCOUNT_Auto>();
        //    try
        //    {
        //        dbManager.Open();
        //        string strSQL = "";
        //        switch (ref_type)
        //        {
        //            case "fim_petitions":
        //                // ฏีกา petition
        //                //strSQL = " SELECT cv.account_remain_receive_org_acc_center as account_seq, 'D' as dr_or_cr, ptd.amount "
        //                //        + " FROM fim_petitions pt "
        //                //        + " LEFT JOIN (select petition_seq "
        //                //        + " , sum(coalesce(amount_real, 0)) as amount "
        //                //        + " from fim_petition_dtls "
        //                //        + " where record_status not like 'D' "
        //                //        + " group by petition_seq "
        //                //        + " ) ptd ON pt.petition_seq = ptd.petition_seq "
        //                //        + " cross join acm_change_value cv on cv.record_status not like 'D' "
        //                //        + " WHERE pt.petition_seq = @ref_seq "
        //                //        + " union all "
        //                //        + " SELECT cv.account_income_between_org as account_seq "
        //                //        + " , 'C' as dr_or_cr, ptd.amount "
        //                //        + " FROM fim_petitions pt "
        //                //        + " LEFT JOIN (select petition_seq "
        //                //        + " , sum(coalesce(amount_real, 0)) as amount "
        //                //        + " from fim_petition_dtls "
        //                //        + " where record_status not like 'D' "
        //                //        + " group by petition_seq "
        //                //        + " ) ptd ON pt.petition_seq = ptd.petition_seq "
        //                //        + " cross join acm_change_value cv on cv.record_status not like 'D' "
        //                //        + " WHERE pt.petition_seq = @ref_seq ";

        //                 strSQL = "SELECT cv.account_remain_receive_org_acc_center as account_seq, 'D' as dr_or_cr, ptd.amount "
        //                         +" , concat(coalesce(acc.account_code, ''), ' : ', coalesce(acc.account_name, ''))  as account_name "
        //                         +" FROM fim_petitions pt "
        //                         +" LEFT JOIN (select petition_seq "
        //                         +" , sum(coalesce(amount_real, 0)) as amount "
        //                         +" from fim_petition_dtls "
        //                         +" where record_status not like 'D' "
        //                         +" group by petition_seq "
        //                         +" ) ptd ON pt.petition_seq = ptd.petition_seq "
        //                         +" cross join acm_change_value cv on cv.record_status not like 'D' "
        //                         +" LEFT JOIN acm_mas_account acc ON cv.account_remain_receive_org_acc_center = acc.account_seq "
        //                         +" WHERE pt.petition_seq = @ref_seq "

        //                         +" union all "

        //                         +" SELECT cv.account_income_between_org as account_seq "
        //                         +" , 'C' as dr_or_cr, ptd.amount "
        //                         +" , concat(coalesce(acc.account_code, ''), ' : ', coalesce(acc.account_name, ''))  as account_name "
        //                         +" FROM fim_petitions pt "
        //                         +" LEFT JOIN (select petition_seq "
        //                         +" , sum(coalesce(amount_real, 0)) as amount "
        //                         +" from fim_petition_dtls "
        //                         +" where record_status not like 'D' "
        //                         +" group by petition_seq "
        //                         +" ) ptd ON pt.petition_seq = ptd.petition_seq "
        //                         +" cross join acm_change_value cv on cv.record_status not like 'D' "
        //                         +" LEFT JOIN acm_mas_account acc ON cv.account_income_between_org = acc.account_seq "
        //                         +" WHERE pt.petition_seq = @ref_seq ";
        //                 break;
        //            case "fim_money_requests":
        //                 // เบิกกรณีเบิกจากใบสำคัญ ต่างๆ        event_seq = 12
        //                 //strSQL = " SELECT cv.account_voucher_remain_pay as account_seq "
        //                 //        +" , 'D' as dr_or_cr, mrd.amount "
        //                 //        +" FROM fim_money_requests mr "
        //                 //        +" LEFT JOIN fim_money_request_dtls mrd ON mr.money_req_seq = mrd.money_req_seq "
        //                 //        +" cross join acm_change_value cv on cv.record_status not like 'D' "
        //                 //        + " WHERE mr.money_req_seq = @ref_seq "
        //                 //        +" union all "
        //                 //        +" SELECT exp.account_seq as account_seq "
        //                 //        +" , 'C' as dr_or_cr, mrd.amount "
        //                 //        +" FROM fim_money_requests mr "
        //                 //        +" LEFT JOIN fim_money_request_dtls mrd ON mr.money_req_seq = mrd.money_req_seq "
        //                 //        +" LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq "
        //                 //        +" LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_oper_seq = recexp.bgm_proj_exp_seq "
        //                 //        +" LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq "
        //                 //        +" cross join acm_change_value cv on cv.record_status not like 'D' "
        //                 //        + " WHERE mr.money_req_seq = @ref_seq ";
        //                strSQL = "   SELECT cv.account_voucher_remain_pay as account_seq "
        //                        +" , 'D' as dr_or_cr, mrd.amount "
        //                        +" , concat(coalesce(acc.account_code, ''), ' : ', coalesce(acc.account_name, ''))  as account_name "
        //                        +" FROM fim_money_requests mr "
        //                        +" LEFT JOIN fim_money_request_dtls mrd ON mr.money_req_seq = mrd.money_req_seq "
        //                        +" cross join acm_change_value cv on cv.record_status not like 'D' "
        //                        +" LEFT JOIN acm_mas_account acc ON cv.account_voucher_remain_pay = acc.account_seq "
        //                        +" WHERE mr.money_req_seq = @ref_seq "

        //                        +" union all "

        //                        +" SELECT exp.account_seq as account_seq "
        //                        +" , 'C' as dr_or_cr, mrd.amount "
        //                        +" , concat(coalesce(acc.account_code, ''), ' : ', coalesce(acc.account_name, ''))  as account_name "
        //                        +" FROM fim_money_requests mr "
        //                        +" LEFT JOIN fim_money_request_dtls mrd ON mr.money_req_seq = mrd.money_req_seq "
        //                        +" LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq "
        //                        +" LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_oper_seq = recexp.bgm_proj_exp_seq "
        //                        +" LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq "
        //                        +" cross join acm_change_value cv on cv.record_status not like 'D' "
        //                        +" LEFT JOIN acm_mas_account acc ON exp.account_seq = acc.account_seq "
        //                        +" WHERE mr.money_req_seq = @ref_seq ";
        //                break;
        //            case "fim_payments":
        //                //strSQL =      "  SELECT 1 as account_seq, 'D' as dr_or_cr, pmd.amount "
        //                //            + " FROM fim_payments pm "
        //                //            + " LEFT JOIN (select pay_seq "
        //                //            + " , sum(coalesce(amount, 0)) as amount "
        //                //            + " from fim_payment_cheques "
        //                //            + " where record_status not like 'D' "
        //                //            + " group by pay_seq "
        //                //            + " ) pmd ON pm.pay_seq = pmd.pay_seq "
        //                //            + " cross join acm_change_value cv on cv.record_status not like 'D' "
        //                //            + " WHERE pm.pay_seq = @ref_seq "
        //                //            + " union all "
        //                //            + "  SELECT cv.account_income_between_org as account_seq " 
        //                //            + " , 'C' as dr_or_cr, pmd.amount "
        //                //            + " FROM fim_payments pm "
        //                //            + " LEFT JOIN (select pay_seq "
        //                //            + " , sum(coalesce(amount, 0)) as amount "
        //                //            + " from fim_payment_cheques "
        //                //            + " where record_status not like 'D' "
        //                //            + " group by pay_seq "
        //                //            + " ) pmd ON pm.pay_seq = pmd.pay_seq "
        //                //            + " cross join acm_change_value cv on cv.record_status not like 'D' "
        //                //            + " WHERE  pm.pay_seq = @ref_seq  ";
        //                strSQL =       " SELECT 1 as account_seq, 'D' as dr_or_cr, pmd.amount "
        //                             + " , concat(coalesce(acc.account_code, ''), ' : ', coalesce(acc.account_name, ''))  as account_name "
        //                             + " FROM fim_payments pm "
        //                             + " LEFT JOIN (select pay_seq "
        //                             + " , sum(coalesce(amount, 0)) as amount "
        //                             + " from fim_payment_cheques "
        //                             + " where record_status not like 'D'"
        //                             + " group by pay_seq "
        //                             + " ) pmd ON pm.pay_seq = pmd.pay_seq "
        //                             + " cross join acm_change_value cv on cv.record_status not like 'D' "
        //                             + " LEFT JOIN acm_mas_account acc ON 1 = acc.account_seq "
        //                             + " WHERE pm.pay_seq = @ref_seq "

        //                             + " union all "

        //                             + " SELECT cv.account_income_between_org as account_seq  "
        //                             + " , 'C' as dr_or_cr, pmd.amount "
        //                             + " , concat(coalesce(acc.account_code, ''), ' : ', coalesce(acc.account_name, ''))  as account_name "
        //                             + " FROM fim_payments pm "
        //                             + " LEFT JOIN (select pay_seq "
        //                             + " , sum(coalesce(amount, 0)) as amount "
        //                             + " from fim_payment_cheques "
        //                             + " where record_status not like 'D' "
        //                             + " group by pay_seq "
        //                             + " ) pmd ON pm.pay_seq = pmd.pay_seq "
        //                             + " cross join acm_change_value cv on cv.record_status not like 'D' "
        //                             + " LEFT JOIN acm_mas_account acc ON 1 = acc.account_seq "
        //                             + " WHERE  pm.pay_seq = @ref_seq  ";
        //                break;
        //            case "fim_receives":
        //                //strSQL =      " SELECT 1 as account_seq, 'D' as dr_or_cr, frd.amount  "
        //                //            + " FROM fim_receives fr "
        //                //            + " LEFT JOIN (select receive_seq "
        //                //            + " , sum(coalesce(amount, 0)) as amount "
        //                //            + " from fim_receive_dtls "
        //                //            + " where record_status not like 'D' "
        //                //            + " group by receive_seq "
        //                //            + " ) frd ON fr.receive_seq = frd.receive_seq "
        //                //            + " cross join acm_change_value cv on cv.record_status not like 'D' "
        //                //            + " WHERE fr.receive_seq = @ref_seq "
        //                //            + " union all "
        //                //            + " SELECT cv.account_income_between_org as account_seq "
        //                //            + " , 'C' as dr_or_cr, frd.amount "
        //                //            + " FROM fim_receives fr "
        //                //            + " LEFT JOIN (select receive_seq "
        //                //            + " , sum(coalesce(amount, 0)) as amount "
        //                //            + " from fim_receive_dtls "
        //                //            + " where record_status not like 'D' "
        //                //            + " group by receive_seq "
        //                //            + " ) frd ON fr.receive_seq = frd.receive_seq "
        //                //            + " cross join acm_change_value cv on cv.record_status not like 'D' "
        //                //            + " WHERE fr.receive_seq = @ref_seq ";
        //                strSQL =  " SELECT 1 as account_seq, 'D' as dr_or_cr, frd.amount  "
        //                         +" , concat(coalesce(acc.account_code, ''), ' : ', coalesce(acc.account_name, ''))  as account_name "
        //                         +" FROM fim_receives fr "
        //                         +" LEFT JOIN (select receive_seq "
        //                         +" , sum(coalesce(amount, 0)) as amount "
        //                         +" from fim_receive_dtls "
        //                         +" where record_status not like 'D' "
        //                         +" group by receive_seq "
        //                         +" ) frd ON fr.receive_seq = frd.receive_seq "
        //                         +" cross join acm_change_value cv on cv.record_status not like 'D' "
        //                         +" LEFT JOIN acm_mas_account acc ON 1 = acc.account_seq "
        //                         +" WHERE fr.receive_seq = @ref_seq "

        //                         +" union all "

        //                         +" SELECT cv.account_income_between_org as account_seq "
        //                         +" , 'C' as dr_or_cr, frd.amount "
        //                         +" , concat(coalesce(acc.account_code, ''), ' : ', coalesce(acc.account_name, ''))  as account_name "
        //                         +" FROM fim_receives fr "
        //                         +" LEFT JOIN (select receive_seq "
        //                         +" , sum(coalesce(amount, 0)) as amount "
        //                         +" from fim_receive_dtls "
        //                         +" where record_status not like 'D' "
        //                         +" group by receive_seq "
        //                         +" ) frd ON fr.receive_seq = frd.receive_seq "
        //                         +" cross join acm_change_value cv on cv.record_status not like 'D' "
        //                         +" LEFT JOIN acm_mas_account acc ON 1 = acc.account_seq "
        //                         +" WHERE fr.receive_seq = @ref_seq ";
        //                break;
        //            case "fim_money_returns":
        //                //strSQL =     "  SELECT 1 as account_seq, 'D' as dr_or_cr, mrd.amount "
        //                //           + " FROM fim_money_returns mr "
        //                //           + " LEFT JOIN (select "
        //                //           + " , sum(coalesce(amount, 0)) as amount "
        //                //           + " from fim_money_return_dtls "
        //                //           + " where record_status not like 'D' "
        //                //           + " group by return_seq "
        //                //           + " ) mrd ON mr.return_seq = mrd.return_seq "
        //                //           + " cross join acm_change_value cv on cv.record_status not like 'D' "
        //                //           + " WHERE mr.return_seq = @ref_seq "
        //                //           + " union all "
        //                //           + "  SELECT cv.account_income_between_org as account_seq "
        //                //           + " , 'C' as dr_or_cr, mrd.amount "
        //                //           + " FROM fim_money_returns mr "
        //                //           + " LEFT JOIN (select return_seq "
        //                //           + " , sum(coalesce(amount, 0)) as amount "
        //                //           + " from fim_money_return_dtls "
        //                //           + " where record_status not like 'D' "
        //                //           + " group by return_seq "
        //                //           + " ) mrd ON mr.return_seq = mrd.return_seq "
        //                //           + " cross join acm_change_value cv on cv.record_status not like 'D' "
        //                //           + " WHERE mr.return_seq = @ref_seq ";
        //                strSQL = "  SELECT 1 as account_seq, 'D' as dr_or_cr, mrd.amount "
        //                         +" , concat(coalesce(acc.account_code, ''), ' : ', coalesce(acc.account_name, ''))  as account_name "
        //                         +" FROM fim_money_returns mr "
        //                         +" LEFT JOIN (select return_seq "
        //                         +" , sum(coalesce(amount, 0)) as amount "
        //                         +" from fim_money_return_dtls "
        //                         +" where record_status not like 'D' "
        //                         +" group by return_seq " 
        //                         +" ) mrd ON mr.return_seq = mrd.return_seq "
        //                         +" LEFT JOIN acm_mas_account acc ON 1 = acc.account_seq "
        //                         +" WHERE mr.return_seq = @ref_seq "

        //                         +" union all "

        //                         +" SELECT cv.account_income_between_org as account_seq "
        //                         +" , 'C' as dr_or_cr, mrd.amount "
        //                         +" , concat(coalesce(acc.account_code, ''), ' : ', coalesce(acc.account_name, ''))  as account_name "
        //                         +" FROM fim_money_returns mr "
        //                         +" LEFT JOIN (select return_seq "
        //                         +" , sum(coalesce(amount, 0)) as amount "
        //                         +" from fim_money_return_dtls "
        //                         +" where record_status not like 'D' "
        //                         +" group by return_seq "
        //                         +" ) mrd ON mr.return_seq = mrd.return_seq "
        //                         +" cross join acm_change_value cv on cv.record_status not like 'D' "
        //                         +" LEFT JOIN acm_mas_account acc ON 1 = acc.account_seq "
        //                         +" WHERE mr.return_seq = @ref_seq ";
        //                break;
        //            case "icm_delivery_note":
        //                strSQL = "";
        //                break;
        //            default:
        //                break;
        //        }
        //        if (strSQL.Length > 0)
        //        {
        //            drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //            if (drc.Count > 0)
        //            {
        //                for (int i = 0; i < drc.Count; i++)
        //                {
        //                    M_ACM_GENERATE_ACCOUNT_Auto info = new M_ACM_GENERATE_ACCOUNT_Auto();
        //                    info.account_seq = cn.ConvertNullInt(drc[i]["account_seq"]);
        //                    info.account_name = drc[i]["account_name"].ToString();
        //                    info.dr_or_cr = drc[i]["dr_or_cr"].ToString();
        //                    info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
        //                    infoList.Add(info);
        //                }
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //    return infoList;
        //}


        //public M_ACM_GENERATE_ACCOUNT_Auto GetTranGroupSeq(string ref_type)
        //{
        //    DataRowCollection drc = null;
        //    M_ACM_GENERATE_ACCOUNT_Auto info = new M_ACM_GENERATE_ACCOUNT_Auto();
        //    dbManager.CreateParameters(1);
        //    dbManager.AddParameters(0, "@ref_type", ref_type);
        //    try
        //    {
        //        dbManager.Open();
        //        string strSQL = " SELECT tran_g.tran_grp_seq ,tran_date "
        //                       +" FROM acm_transection_group tran_g "
        //                       +" LEFT JOIN acm_transection tran ON "
        //                       +" tran_g.tran_grp_seq = tran.tran_grp_seq "
        //                       +" AND tran.record_status not like 'D' "
        //                       +" WHERE ref_type = @ref_type "
        //                       +" AND tran_g.record_status not like 'D' ";

        //        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //        info.tran_grp_seq = Convert.ToInt32(drc[0]["tran_grp_seq"].ToString());
        //        info.tran_date = cn.ConvertNullDateTime(drc[0]["tran_date"]);
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //    return info;
        //}

        public Boolean CheckRefType(string ref_type)
        {
            Boolean pRef_type = false;
            if (ref_type != "")
            {
                DataRowCollection drc = null;
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@ref_type", ref_type);

                try
                {
                    dbManager.Open();
                    string strSQL = " SELECT COUNT(ref_type) as count_ref_type "
                                   + " FROM acm_transection_group "
                                   + " WHERE ref_type = @ref_type ";

                    drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                    if (drc.Count > 0)
                    {
                        if (Convert.ToInt32(drc[0]["count_ref_type"].ToString()) > 0)
                            pRef_type = false;
                        else
                            pRef_type = true;
                    }
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
                finally
                {
                    dbManager.Dispose();
                }
            }
            else
                pRef_type = false;

            return pRef_type;
        }

        public Int32 MaxTransactionGroup()
        {
            DataRowCollection drc = null;
            Int32 tran_group_code = 0;
            try
            {
                dbManager.Open();
                string strSQL = " SELECT COUNT(tran_grp_seq) as Max "
                              + " FROM acm_transection_group ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                if (drc.Count > 0)
                {
                    tran_group_code = Convert.ToInt32(drc[0]["Max"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return tran_group_code;
        }

        public void InsertTransactionGroup(M_ACM_GENERATE_ACCOUNT_Auto info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@tran_grp_code", info.tran_grp_code);
                dbManager.AddParameters(1, "@ref_type", info.ref_type);
                dbManager.AddParameters(2, "@record_status", info.record_status);
                dbManager.AddParameters(3, "@created_by", info.created_by);
                dbManager.AddParameters(4, "@created_when", info.created_when);


                strSQL = "INSERT INTO acm_transection_group "
                    + " (tran_grp_code,ref_type,record_status "
                    + ", created_by,created_when)"
                    + "  VALUES("
                    + "  @tran_grp_code"
                    + ", @ref_type"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when"
                    + " )";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public void UpdateRefType(string ref_type, int ref_seq, int tran_grp_seq)
        {
            string strSQL = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                //dbManager.AddParameters(0, "@ref_type", info.ref_type);
                dbManager.AddParameters(0, "@tran_grp_seq", tran_grp_seq);
                dbManager.AddParameters(1, "@ref_seq", ref_seq);

                switch (ref_type)
                {
                    case "fim_petitions":
                        strSQL = " UPDATE " + ref_type
                                + " SET tran_grp_seq=@tran_grp_seq "
                                + " WHERE petition_seq=@ref_seq AND "
                                + " record_status not like 'D' ";
                        break;
                    case "fim_money_requests":
                        strSQL = " UPDATE " + ref_type
                                + " SET tran_grp_seq=@tran_grp_seq "
                                + " WHERE money_req_seq=@ref_seq AND "
                                + " record_status not like 'D' ";
                        break;
                    case "fim_payments":
                        strSQL = " UPDATE " + ref_type
                                + " SET tran_grp_seq=@tran_grp_seq "
                                + " WHERE pay_seq=@ref_seq AND"
                                + " record_status not like 'D' ";
                        break;
                    case "fim_receives":
                        strSQL = " UPDATE " + ref_type
                                + " SET tran_grp_seq=@tran_grp_seq "
                                + " WHERE receive_seq=@ref_seq AND"
                                + " record_status not like 'D' ";
                        break;
                    case "fim_money_returns":
                        strSQL = " UPDATE " + ref_type
                                + " SET tran_grp_seq=@tran_grp_seq "
                                + " WHERE return_seq=@ref_seq AND"
                                + " record_status not like 'D' ";
                        break;
                    case "icm_delivery_note":
                        strSQL = " UPDATE IcmDeliveryNote"
                                + " SET TranGrpId=@tran_grp_seq "
                                + " WHERE DnId=@ref_seq AND"
                                + " RecordStatus not like 'D' ";
                        break;
                    case "fim_transfers":
                        strSQL = " UPDATE fim_transfers"
                                + " SET tran_grp_seq=@tran_grp_seq "
                                + " WHERE transfer_seq=@ref_seq AND"
                                + " record_status not like 'D' ";
                        break;
                }

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string GetNextTranCode(string bgm_source_seq, string bgm_year, string tran_type, DBManager dbManager)
        {
            DataTable dtb = null;
            string strNextTranCode = tran_type + "-" + (!string.IsNullOrEmpty(bgm_source_seq) ? (bgm_source_seq == "1" ? "1" : "2") : "3") + "-" + bgm_year + "-00001";
            try
            {
                //dbManager.Open();

                string strSQL = "SELECT max(CONVERT(VARCHAR(50),tran_code)) as tran_code"
                    + " from acm_transection"
                    + " where record_status not like 'D'"
                    + " and bgm_source_seq = " + (!string.IsNullOrEmpty(bgm_source_seq) ? bgm_source_seq : "1")
                    + " and bgm_year = " + bgm_year
                    + " and tran_type = '" + tran_type + "'"

                    + " group by bgm_source_seq, bgm_year, tran_type";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    if (dtb.Rows[0]["tran_code"] != DBNull.Value)
                    {
                        strNextTranCode = tran_type + "-" + (!string.IsNullOrEmpty(bgm_source_seq) ? (bgm_source_seq == "1" ? "1" : "2") : "3") + "-" + bgm_year + "-" + (Convert.ToInt32(dtb.Rows[0]["tran_code"].ToString().Split('-')[3]) + 1).ToString("00000");
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                //dbManager.Dispose();
            }
            return strNextTranCode;
        }

        public DataTable GetQuotationData(string DnId)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@icm_dn_seq", DnId);
            // เช็คว่ามีงานระหว่างทำในแต่ละงวดหรือไม่
            string strSQL = @"
select poii.BidderQuoId, coalesce(dni.HaveItem, 1) as HaveItem
from IcmDeliveryNoteItem dni
left join IcmPurchaseOrderInstallmentItem poii on dni.PoInstallItemId = poii.PoInstallItemId
where dni.DnId = @icm_dn_seq
";
            DataTable dtbQuotation = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            return dtbQuotation;
        }

        public int GetHaveItemRowCount(string BidderQuoId)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@BidderQuoId", BidderQuoId);
            string strSQL = @"
select count(*) as row_count
from IcmPurchaseOrderInstallmentItem poii
left join IcmDeliveryNoteItem dni on poii.PoInstallItemId = dni.PoInstallItemId
where poii.BidderQuoId = @BidderQuoId
and HaveItem = 0
";
            int nRowCount = Convert.ToInt32(dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0]);
            return nRowCount;
        }

        public DataTable GetDnItemData(string DnId, string BidderQuoId)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@DnId", DnId);
            dbManager.AddParameters(1, "@BidderQuoId", BidderQuoId);

            string strSQL = @"
select ba.wait_account_seq as account1, ba.suspense_input_tax_account_seq as account3, ba.direct_pay_account_seq as account4
, acc1.account_code as code1, acc3.account_code as code3, acc4.account_code as code4
, acc1.account_name as name1, acc3.account_name as name3, acc4.account_name as name4
, coalesce(dni.NetAmount, 0) as NetAmount, coalesce(dni.VatAmount, 0) as VatAmount
from IcmDeliveryNoteItem dni
left join IcmPurchaseOrderInstallmentItem poii on dni.PoInstallItemId = poii.PoInstallItemId
cross join acm_mas_base_account ba
left join acm_mas_account acc1 on ba.wait_account_seq = acc1.account_seq
left join acm_mas_account acc3 on ba.suspense_input_tax_account_seq = acc3.account_seq
left join acm_mas_account acc4 on ba.direct_pay_account_seq = acc4.account_seq
where poii.BidderQuoId = @BidderQuoId
and dni.DnId = @DnId
";
            DataTable dtbDni = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            return dtbDni;
        }

        public DataTable GetDnItemData2(string BidderQuoId)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@BidderQuoId", BidderQuoId);

            string strSQL = @"
select sum(coalesce(dni.NetAmount, 0)) as Amount
, sum(coalesce(dni.VatAmount, 0)) as VatAmount
, ba.wait_account_seq as account_seq1
, case when ri.ResItemId is not null and rg.ResTypeId <> 1
	then rg.AssetAccountId
	else ex.account_seq
	end as account_seq2
, acc3.account_code as account_code1
, case when ri.ResItemId is not null and rg.ResTypeId <> 1
	then acc1.account_code
	else acc2.account_code
	end as account_code2
, acc3.account_name as account_name1
, case when ri.ResItemId is not null and rg.ResTypeId <> 1
	then acc1.account_name
	else acc2.account_name
	end as account_name2
from IcmPurchaseOrderInstallmentItem poii
left join IcmDeliveryNoteItem dni on poii.PoInstallItemId = dni.PoInstallItemId
left join IcmDeliveryNote dn on dni.DnId = dn.DnId
left join IcmBidderQuotation q on poii.BidderQuoId = q.BidderQuoId
left join IcmPurchaseRequestItem ri on q.PrItemId = ri.PrItemId
left join bgm_mas_expense ex on ri.BgExpId = ex.bgm_exp_seq
left join IcmMasResourceItem mi on ri.ResItemId = mi.ResItemId
left join IcmMasResourceSubGroup sg on mi.ResSubGrpId = sg.ResSubGrpId
left join IcmMasResourceGroup rg on sg.ResGrpId = rg.ResGrpId
LEFT JOIN acm_mas_account acc1 ON rg.AssetAccountId = acc1.account_seq
LEFT JOIN acm_mas_account acc2 ON ex.account_seq = acc2.account_seq
cross join acm_mas_base_account ba
LEFT JOIN acm_mas_account acc3 ON ba.wait_account_seq = acc3.account_seq
where poii.BidderQuoId = @BidderQuoId
and dn.RecordStatus = 'A'
group by ba.wait_account_seq, ri.ResItemId, rg.ResTypeId, rg.AssetAccountId, ex.account_seq
    , acc1.account_code, acc2.account_code, acc3.account_code, acc1.account_name, acc2.account_name, acc3.account_name
";
            DataTable dtbDni = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            return dtbDni;
        }

        public IList<M_ACM_GENERATE_ACCOUNT_Auto> AddDnItemToList(DataTable dtbDni, IList<M_ACM_GENERATE_ACCOUNT_Auto> infoList)
        {
            if (dtbDni.Rows.Count > 0)
            {
                M_ACM_GENERATE_ACCOUNT_Auto info1 = new M_ACM_GENERATE_ACCOUNT_Auto()
                {
                    account_seq = cn.ConvertNullInt(dtbDni.Rows[0]["account1"]),
                    dr_or_cr = "Dr",
                    account_code = dtbDni.Rows[0]["code1"].ToString(),
                    account_name = dtbDni.Rows[0]["name1"].ToString(),
                    amount_dr = cn.ConvertNullDouble(dtbDni.Rows[0]["NetAmount"]) - cn.ConvertNullDouble(dtbDni.Rows[0]["VatAmount"]),
                    amount_cr = 0,
                };

                infoList.Add(info1);

                if (cn.ConvertNullDouble(dtbDni.Rows[0]["VatAmount"]) != 0)
                {
                    M_ACM_GENERATE_ACCOUNT_Auto info2 = new M_ACM_GENERATE_ACCOUNT_Auto()
                    {
                        account_seq = cn.ConvertNullInt(dtbDni.Rows[0]["account3"]),
                        dr_or_cr = "Dr",
                        account_code = dtbDni.Rows[0]["code3"].ToString(),
                        account_name = dtbDni.Rows[0]["name3"].ToString(),
                        amount_dr = cn.ConvertNullDouble(dtbDni.Rows[0]["VatAmount"]),
                        amount_cr = 0,
                    };

                    infoList.Add(info2);
                }

                //M_ACM_GENERATE_ACCOUNT_Auto info3 = new M_ACM_GENERATE_ACCOUNT_Auto()
                //{
                //    account_seq = cn.ConvertNullInt(dtbDni.Rows[0]["account4"]),
                //    dr_or_cr = "Cr",
                //    account_code = dtbDni.Rows[0]["code4"].ToString(),
                //    account_name = dtbDni.Rows[0]["name4"].ToString(),
                //    amount_dr = 0,
                //    amount_cr = cn.ConvertNullDouble(dtbDni.Rows[0]["NetAmount"]),
                //};

                //infoList.Add(info3);
            }

            return infoList;
        }

        public IList<M_ACM_GENERATE_ACCOUNT_Auto> AddDnItemToList2(DataTable dtbDni2, IList<M_ACM_GENERATE_ACCOUNT_Auto> infoList)
        {
            double dAmount = cn.ConvertNullDouble(dtbDni2.Rows[0][0]) ?? 0;
            double dVatAmount = cn.ConvertNullDouble(dtbDni2.Rows[0][1]) ?? 0;
            string strSeq1 = dtbDni2.Rows[0][2].ToString();
            string strSeq2 = dtbDni2.Rows[0][3].ToString();
            string strCode1 = dtbDni2.Rows[0][4].ToString();
            string strCode2 = dtbDni2.Rows[0][5].ToString();
            string strName1 = dtbDni2.Rows[0][6].ToString();
            string strName2 = dtbDni2.Rows[0][7].ToString();

            if (dtbDni2.Rows.Count > 0)
            {
                M_ACM_GENERATE_ACCOUNT_Auto info1 = new M_ACM_GENERATE_ACCOUNT_Auto()
                {
                    account_seq = Convert.ToInt32(strSeq2),
                    dr_or_cr = "Dr",
                    account_code = strCode2,
                    account_name = strName2,
                    amount_dr = dAmount - dVatAmount,
                    amount_cr = 0,
                };

                infoList.Add(info1);

                M_ACM_GENERATE_ACCOUNT_Auto info2 = new M_ACM_GENERATE_ACCOUNT_Auto()
                {
                    account_seq = Convert.ToInt32(strSeq1),
                    dr_or_cr = "Cr",
                    account_code = strCode1,
                    account_name = strName1,
                    amount_dr = 0,
                    amount_cr = dAmount - dVatAmount,
                };

                infoList.Add(info2);
            }

            return infoList;
        }

        public DataTable GetPenaltyAmount(string DnId)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@DnId", DnId);

            string strSQL = @"
select pen.PenaltyFinalAmount, dni.NetAmount
, acc1.account_seq as account_seq1, acc1.account_code as account_code1, acc1.account_name as account_name1
, acc2.account_seq as account_seq2, acc2.account_code as account_code2, acc2.account_name as account_name2
from IcmDeliveryNote dn
left join (select DnId, sum(coalesce(PenaltyFinalAmount, 0)) as PenaltyFinalAmount
    from IcmDeliveryNotePenalty
    group by DnId
    ) pen on dn.DnId = pen.DnId
left join (select DnId, sum(coalesce(NetAmount, 0)) as NetAmount
    from IcmDeliveryNoteItem
    group by DnId
    ) dni on dn.DnId = dni.DnId
cross join acm_mas_base_account ba
LEFT JOIN acm_mas_account acc1 ON ba.penalty_account_seq = acc1.account_seq
LEFT JOIN acm_mas_account acc2 ON ba.direct_pay_account_seq = acc2.account_seq

where dn.DnId = @DnId
--group by acc1.account_seq, acc1.account_code, acc1.account_name
";
            DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            return dtb;
        }

        public bool CheckTransactionExist(string ref_no, string tran_type)
        {
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@ref_no", ref_no);
                dbManager.AddParameters(1, "@tran_type", tran_type);

                string strSQL = "SELECT COUNT(*) as count"
                    + " FROM acm_transection"
                    + " where record_status <> 'D'"
                    + " and ref_no = @ref_no"
                    + (!string.IsNullOrEmpty(tran_type) ? " and tran_type = @tran_type" : "");

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    int count = Convert.ToInt32(dtb.Rows[0][0]);

                    return count > 0;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}